package com.cennavi.bigdata.baotou.dao.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.cennavi.bigdata.baotou.dao.ViewDao;
import com.cennavi.bigdata.baotou.scripts.ActualTimeRequest;
import com.cennavi.bigdata.baotou.scripts.KakouTraffic;
import com.cennavi.bigdata.baotou.util.DateUtils;
import com.cennavi.bigdata.baotou.util.PropertiesUtil;
import com.mongodb.*;
import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.GeometryFactory;
import com.vividsolutions.jts.geom.LineString;
import com.vividsolutions.jts.geom.Point;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.Reader;
import java.io.StringReader;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import static java.lang.System.out;

/**
 * Created by zuoweiguang on 2016/9/7.
 */
@Repository("viewDao")
public class ViewDaoImpl extends ActualTimeRequest implements ViewDao {

    private static JSONObject prop = PropertiesUtil.getProperties();
//    private static ServerAddress serverAddress = new ServerAddress(prop.getString("mongoHost"), prop.getInteger("mongoPort"));
//    private static MongoClient mongoClient = null;
//    private static DB db = null;

    @Autowired
    private MongoTemplate mongoTemplate;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private JdbcTemplate jdbcTemplatePG;

//    public DB getMongoDb() {
//        if (db == null) {
//            List mongoCredentialList = new ArrayList();
//            mongoCredentialList.add(MongoCredential.createCredential("sa", "admin", "mm@2016".toCharArray()));
//            mongoClient = new MongoClient(serverAddress, mongoCredentialList);
//            db = mongoClient.getDB(prop.getString("mongoDb"));
//        }
//        return db;
//    }

//    @Override
//    public DBCollection getCollection(String collectionName) {
//        db = getMongoDb();
//        DBCollection col = db.getCollection(collectionName);
//        return col;
//    }

    @Override
    public String testOracle() {
        try {
            String result = jdbcTemplate.getDataSource().getConnection().toString();
            return "connect oracle success:" + result;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "connect oracle failed!!";
    }

    @Override
    public List<DBObject> getTraffic(int z, int x, int y) {
        List<DBObject> trafficList = new ArrayList<>();
        try {

            BasicDBObject query = new BasicDBObject();

            if (z > 15) {
                //15级以后按当前屏幕瓦片查询，避免数据量太大加载速度慢
                String key = "tile_" + z;
                String value = x + "_" + y;
                query.append(key, value);
            } else {
                //15级以前用层级前置的方式查询
                //DBObject filter = getFilter(z);
                DBObject filter = getRticKindFilter(z);
                if (filter != null) {
//                query.append("functionclass", filter);
                    query.append("level", filter);
                }
            }

            DBCollection col = mongoTemplate.getCollection(prop.getString("trafficStatus"));
            DBCursor cursor = col.find(query).sort(new BasicDBObject("level", 1));
            while (cursor.hasNext()) {
                DBObject traffic = cursor.next();
//                String rticId = (String) traffic.get("rtic_id");
                trafficList.add(traffic);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return trafficList;
        }
    }

    @Override
    public String getCongestionHotspots(String url) {
        //调用尤波提供的【拥堵热点】服务接口
        return sendGet(url);
    }

    @Override
    public JSONObject getRoadNameByRoadId(String roadId) {
        SqlRowSet resultSet =null;
        JSONObject jsonObject = new JSONObject();
        try {
            String  sql = "select ROADNAME,STARTNAME,ENDNAME,GRADE from COMM_SEG_INFO where id=? ";
            resultSet = jdbcTemplate.queryForRowSet(sql,roadId);
            while (resultSet.next()){
                jsonObject.put("road_name",resultSet.getString("ROADNAME"));
                jsonObject.put("road_start",resultSet.getString("STARTNAME"));
                jsonObject.put("road_end",resultSet.getString("ENDNAME"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return jsonObject;
    }

    @Override
    public List<DBObject> getTrafficHeatPoint() {
        List<DBObject> trafficList = new ArrayList<>();
        try {
            BasicDBObject condition = new BasicDBObject();
            //
            condition.append("status", new BasicDBObject("$gt", 1));
//            condition.append("link_name", new BasicDBObject("$ne", null));
            condition.append("sectionCntList.degreeLen", new BasicDBObject("$ne", 0));
            List<Integer> kindList = new ArrayList<>();
            kindList.add(1);
            kindList.add(2);
            kindList.add(3);
            kindList.add(4);
            kindList.add(5);
            kindList.add(6);
            condition.append("rtic_link_kind", new BasicDBObject("$in", kindList));

            BasicDBObject sortCondition = new BasicDBObject();
            sortCondition.append("status", -1);
            sortCondition.append("sectionCntList.degreeLen", -1);

            DBCollection col = mongoTemplate.getCollection(prop.getString("trafficStatus"));
            DBCursor cursor = col.find(condition).sort(sortCondition).limit(50);
            while (cursor.hasNext()) {
                DBObject event = cursor.next();
                trafficList.add(event);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return trafficList;
        }
    }

    @Override
    public List<DBObject> getTrafficControl(String controlType, Date todayStartTime) {
        List<DBObject> trafficControlList = new ArrayList<>();
        try {
            DBCollection col = mongoTemplate.getCollection("traffic_control");
            BasicDBObject cond = new BasicDBObject();
            BasicDBList values = new BasicDBList();
            if (StringUtils.isEmpty(controlType)) {
                values.add(new BasicDBObject("end_time", new BasicDBObject("$gte", DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayStartTime))));
                values.add(new BasicDBObject("end_time", new BasicDBObject("$gte", DateUtils.DateFormatUnit.DATE.getDateStr(todayStartTime))));
            } else if (controlType.equals("not_temporary")) {
                cond.put("$gte", DateUtils.DateFormatUnit.DATE.getDateStr(todayStartTime));
            } else {
                cond.put("$gte", DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayStartTime));
            }

            BasicDBObject object = new BasicDBObject();
            if (StringUtils.isEmpty(controlType)) {
                object.put("$or", values);
            } else {
                object.put("control_type", controlType);
                object.put("end_time", cond);
            }
            DBCursor cursor = col.find(object, new BasicDBObject("_id", 0)).sort(new BasicDBObject("update_time", -1));
            while (cursor.hasNext()) {
                DBObject trafficControl = cursor.next();
                trafficControlList.add(trafficControl);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return trafficControlList;
        }
    }

    @Override
    public int insertTrafficControl(String jsonData) {
        try {
            DBCollection col = mongoTemplate.getCollection("traffic_control");
            BasicDBObject data = BasicDBObject.parse(jsonData);
            data.put("update_time", DateUtils.DateFormatUnit.DATE_TIME.getDateStr(new Date()));
            col.insert(data);
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    @Override
    public List<DBObject> getConstructionOccupation(Date todayStartTime) {
        List<DBObject> constructionOccupationList = new ArrayList<>();
        try {
            DBCollection col = mongoTemplate.getCollection("construction_occupation");
            BasicDBObject cond = new BasicDBObject();
            cond.put("$gte", DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayStartTime));
            BasicDBObject object = new BasicDBObject();
            object.put("end_time", cond);
            DBCursor cursor = col.find(object, new BasicDBObject("_id", 0)).sort(new BasicDBObject("update_time", -1));
            while (cursor.hasNext()) {
                DBObject constructionOccupation = cursor.next();
                constructionOccupationList.add(constructionOccupation);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return constructionOccupationList;
        }
    }

    @Override
    public int insertConstructionOccupation(String jsonData) {
        try {
            DBCollection col = mongoTemplate.getCollection("construction_occupation");
            BasicDBObject data = BasicDBObject.parse(jsonData);
            data.put("update_time", DateUtils.DateFormatUnit.DATE_TIME.getDateStr(new Date()));
            col.insert(data);
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }

    @Override
    public String getRoadIndexRT(String roadIndexRTUrl) {
        SqlRowSet resultSet = null;
        List list = new ArrayList();
        String  sql = "select id,roadname,startname,endname from INDEX_ROAD_INFO ";
        resultSet = jdbcTemplate.queryForRowSet(sql);
        JSONObject roadJson = new JSONObject();
        while (resultSet.next()){
            JSONObject jsonObject = new JSONObject();
            String startname = resultSet.getString("startname");
            String roadname = resultSet.getString("roadname");
            String endname = resultSet.getString("endname");
            String id = resultSet.getString("id");
            jsonObject.put("startname",startname);
            jsonObject.put("endname",endname);
            jsonObject.put("roadname",roadname);
            roadJson.put(id,jsonObject);
        }
        String sendGet = sendGet(roadIndexRTUrl);
        JSONArray objects = JSONArray.parseArray(sendGet);
        for (Object obj:objects) {
            JSONObject jsonObject = JSONObject.parseObject(obj.toString());
            String id = jsonObject.getString("id");
            JSONObject jsonObject1 = roadJson.getJSONObject(id);
            String startname = jsonObject1.getString("startname");
            String endname = jsonObject1.getString("endname");
            String roadName = jsonObject1.getString("roadname");
            String  newRoadName = roadName+"("+startname+","+endname+")";
            jsonObject.put("roadname",newRoadName);
            list.add(jsonObject);
        }
        return list.toString();
    }

    @Override
    public String getDCIndexRT(String DCIndexRTUrl) {
        return sendGet(DCIndexRTUrl);
    }

    @Override
    public String getHotIndexRT(String HotIndexRTUrl) {
        return sendGet(HotIndexRTUrl);
    }

    @Override
    public Map<String, JSONObject> getPatternStatus(String PatternUrl) {
//        JSONArray patternList = new JSONArray();
        BufferedReader in = null;
        Map<String, JSONObject> patternStatus = new HashMap<>();
        try {
            //请求外部接口，获取pattern预测数据
            String result = sendGet4Pattern(PatternUrl);
            //将数据转换为json结构
            Reader reader = new StringReader(result);
            in = new BufferedReader(reader);
            String line;
            if ((line = in.readLine()) != null) {
                try {
                    String[] lines = line.split("\\|");
                    for (int i = 0; i < lines.length; i++) {
                        line = lines[i];
                        String[] data = line.split(",");
                        String mesh = data[0];
                        String kind = data[2];
                        String number = data[1];
                        String length = data[3];
                        String speed = data[4];
                        String los = data[5];
                        //rtic不够4位，前面补0
                        while (number.length() != 4) {
                            number = "0" + number;
                        }
                        String rticId = mesh + kind + number;
                        JSONObject patternJson = new JSONObject();
                        patternJson.put("los", Integer.valueOf(los));
                        patternJson.put("speed", Integer.valueOf(speed));
                        patternJson.put("length", Integer.valueOf(length));
                        patternJson.put("rticId", rticId);
                        patternStatus.put(rticId, patternJson);
//                        JSONObject patternJson = new JSONObject();
//                        patternJson.put("mesh", lines[0]);
//                        patternJson.put("number", lines[1]);
//                        patternJson.put("kind", lines[2]);
//                        patternJson.put("length", lines[3]);
//                        patternJson.put("speed", lines[4]);
//                        patternJson.put("los", lines[5]);
//                        patternJson.put("rtic_id", lines[0]+lines[2]+lines[1]);
//                        patternList.add(patternJson);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (in != null) {
                    in.close();
                }
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }
        // 使用finally块来关闭输入流
        finally {
            try {
                if (in != null) {
                    in.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return patternStatus;
    }

    @Override
    public List<DBObject> getPattern(int z, int x, int y) {
        List<DBObject> patternList = new ArrayList<>();
        try {

            BasicDBObject query = new BasicDBObject();

            if (z > 15) {
                //15级以后按当前屏幕瓦片查询，避免数据量太大加载速度慢
                String key = "tile_" + z;
                String value = x + "_" + y;
                query.append(key, value);
            } else {
                //15级以前用层级前置的方式查询
                //DBObject filter = getFilter(z);
                DBObject filter = getRticKindFilter(z);
                if (filter != null) {
//                query.append("functionclass", filter);
                    query.append("level", filter);
                }
            }

            DBCollection col = mongoTemplate.getCollection(prop.getString("patternStatus"));
            DBCursor cursor = col.find(query).sort(new BasicDBObject("level", 1));
            while (cursor.hasNext()) {
                DBObject pattern = cursor.next();
                patternList.add(pattern);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return patternList;
        }
    }

    @Override
    public String getTeamIndexRT(String teamIndexRTUrl) {
        return sendGet(teamIndexRTUrl);
    }

    @Override
    public String getDCIndexByYear(String dcIndexByYearUrl) {
        JSONObject yearIndex = null;
        try {
            //根据历史年指数返回的结果进行过滤，只取【市中心】的记录
            String result = sendGet(dcIndexByYearUrl);
            JSONArray jsonArray = (JSONArray) JSONArray.parse(result);
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = (JSONObject) jsonArray.get(i);
                //{"dccode":"150298","dcname":"市中心","avgspeed":"40.08,39.93,39.56,39.31,39.81,37.69,37.79,37.60,37.72,38.82,38.88,38.47","c_index":"2.08,2.10,2.17,2.25,2.30,2.59,2.57,2.60,2.61,2.32,2.31,2.37"}
                String dccode = jsonObject.getString("dccode");
                if (dccode.equals("150298")) {
                    yearIndex = jsonObject;
                    break;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (yearIndex != null) {
                return yearIndex.toString();
            } else {
                return new JSONObject().toString();
            }
        }
    }

    @Override
    public String getDCIndexByMonth(String dcIndexByMonthUrl) {
        JSONObject monthIndex = null;
        try {
            //根据历史月度指数返回的结果进行过滤，只取【市中心】的记录
            String result = sendGet(dcIndexByMonthUrl);
            JSONArray jsonArray = (JSONArray) JSONArray.parse(result);
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = (JSONObject) jsonArray.get(i);
                //{"dccode":"150298","dcname":"市中心","avgspeed":"39.84,39.62,39.63,39.66,39.63,40.00,42.82,41.99,42.18,42.11,41.80,34.46,35.61,38.47,39.53,40.02,40.05,40.14,39.58,39.90,40.14,40.26,40.07,40.10,39.91,39.93,40.40,40.17","c_index":"2.15,2.17,2.16,2.18,2.23,2.12,1.68,1.73,1.71,1.73,1.79,3.22,2.78,2.37,2.16,2.08,2.06,2.01,2.11,2.05,2.02,2.01,2.04,2.03,2.05,2.09,2.02,2.06"}
                String dccode = jsonObject.getString("dccode");
                if (dccode.equals("150298")) {
                    monthIndex = jsonObject;
                    break;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (monthIndex != null) {
                return monthIndex.toString();
            } else {
                return new JSONObject().toString();
            }
        }
    }

    @Override
    public String getDCIndexByHoli(String dcIndexByHoliUrl) {
        JSONObject holidayIndex = null;
        try {
            //根据历史节假日指数返回的结果进行过滤，只取【市中心】的记录
            String result = sendGet(dcIndexByHoliUrl);
            JSONArray jsonArray = (JSONArray) JSONArray.parse(result);
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = (JSONObject) jsonArray.get(i);
                String dccode = jsonObject.getString("dccode");
                if (dccode.equals("150298")) {
                    holidayIndex = jsonObject;
                    break;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (holidayIndex != null) {
                return holidayIndex.toString();
            } else {
                return new JSONObject().toString();
            }
        }
    }

    @Override
    public String getCongestionRoadRank(String congestionRoadRankUrl) {
        List<JSONObject> resultList = new ArrayList<>();
        JSONObject roadJson = new JSONObject();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String result = sendGet(congestionRoadRankUrl);
            JSONArray jsonArray = (JSONArray) JSONArray.parse(result);
            connection = jdbcTemplate.getDataSource().getConnection();
            String  sql = "select ROADNAME,STARTNAME,ENDNAME,id from COMM_SEG_INFO where id IN " +
                    "("+ org.apache.commons.lang.StringUtils.repeat("?", ",", jsonArray.size()) + ")";
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = (JSONObject) jsonArray.get(i);
                String roadId = jsonObject.getString("roadId");
                statement.setString(i + 1, roadId);
            }
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                String roadname = resultSet.getString("ROADNAME");
                String startname = resultSet.getString("STARTNAME");
                String endname = resultSet.getString("ENDNAME");
                String roadid = resultSet.getString("id");

                JSONObject jsonObject = new JSONObject();
                jsonObject.put("road_name",roadname);
                jsonObject.put("road_start",startname);
                jsonObject.put("road_end",endname);
                roadJson.put(roadid,jsonObject);
            }

            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = (JSONObject) jsonArray.get(i);
                String roadId = jsonObject.getString("roadId");
                String counts = jsonObject.getString("counts");
                if (roadJson.containsKey(roadId)){
                    JSONObject jsonObject1 = (JSONObject) roadJson.get(roadId);
                    String roadname = jsonObject1.getString("road_name");
                    String startname = jsonObject1.getString("road_start");
                    String endname = jsonObject1.getString("road_end");
                    JSONObject resultObj = new JSONObject();
                    resultObj.put("roadName", roadname);
                    resultObj.put("roadStart", startname);
                    resultObj.put("roadEnd", endname);
                    resultObj.put("roadId", roadId);
                    resultObj.put("counts", counts);
                    resultList.add(resultObj);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeOracle(resultSet,statement,connection);
            return resultList.toString();
        }
    }

    @Override
    public String getCongestionRoadStatis(String congestionRoadStatisUrl) {
        List<JSONObject> resultList = new ArrayList<>();
        JSONObject roadJson = new JSONObject();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String result = sendGet(congestionRoadStatisUrl);
            JSONArray jsonArray = (JSONArray) JSONArray.parse(result);
            connection = jdbcTemplate.getDataSource().getConnection();
            String  sql = "select roadapplication,ROADSTARTENDLONT,ID from ROAD_PIECEWISE_DATA where id IN " +
                    "("+ org.apache.commons.lang.StringUtils.repeat("?", ",", jsonArray.size()) + ")";
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = (JSONObject) jsonArray.get(i);
                String roadId = jsonObject.getString("roadId");
                statement.setString(i + 1, roadId);
            }
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                String roadapplication = resultSet.getString("roadapplication");
                String roadstartendlont = resultSet.getString("ROADSTARTENDLONT");
                String roadid = resultSet.getString("id");
                String[] split = roadapplication.split("\\(");
                String[] split1 = split[1].split(",");
                String[] lontSplit = roadstartendlont.split(" ")[0].split(";");
                double longitudes = Double.parseDouble(lontSplit[0]);
                double lontiues = Double.parseDouble(lontSplit[1]);
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("road_name",split[0]);
                jsonObject.put("road_start",split1[0]);
                jsonObject.put("road_end",split1[1].split("\\)")[0]);
                jsonObject.put("longitudes",longitudes);
                jsonObject.put("lontiues",lontiues);
                roadJson.put(roadid,jsonObject);
            }
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = (JSONObject) jsonArray.get(i);
                String roadId = jsonObject.getString("roadId");
                String counts = jsonObject.getString("counts");
                if (roadJson.containsKey(roadId)){
                    JSONObject jsonObject1 = (JSONObject) roadJson.get(roadId);
                    String roadname = jsonObject1.getString("road_name");
                    String startname = jsonObject1.getString("road_start");
                    String endname = jsonObject1.getString("road_end");
                    double longitudes = jsonObject1.getDouble("longitudes");
                    double lontiues = jsonObject1.getDouble("lontiues");
                    List<List<Double>> rticGeoJson = new ArrayList<>();
                    List<Double> coordinate = new ArrayList<>();
                    coordinate.add(longitudes);
                    coordinate.add(lontiues);
                    rticGeoJson.add(coordinate);
                    JSONObject geoJson = new JSONObject();
                    geoJson.put("type", "LineString");
                    geoJson.put("coordinates", rticGeoJson);

                    JSONObject resultObj = new JSONObject();
                    resultObj.put("roadName", roadname);
                    resultObj.put("roadStart", startname);
                    resultObj.put("roadEnd", endname);
                    resultObj.put("roadId", roadId);
                    resultObj.put("counts", counts);
                    resultObj.put("geometry", geoJson);
                    resultList.add(resultObj);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeOracle(resultSet,statement,connection);
            return resultList.toString();
        }
//        try {
//            DBCollection col = mongoTemplate.getCollection("roadinfo");
//
//            String result = sendGet(congestionRoadStatisUrl);
//            JSONArray jsonArray = (JSONArray) JSONArray.parse(result);
//
//            for (int i = 0; i < jsonArray.size(); i++) {
//                JSONObject jsonObject = (JSONObject) jsonArray.get(i);
//                String roadId = jsonObject.getString("roadId");
//                String counts = jsonObject.getString("counts");
//
//                DBObject roadInfo = col.findOne(new BasicDBObject("road_id", roadId));
//                String roadName = (String) roadInfo.get("road_name");
//                String roadStart = (String) roadInfo.get("road_start");
//                String roadEnd = (String) roadInfo.get("road_end");
//                List<DBObject> road_rtic_list = (List<DBObject>) roadInfo.get("road_rtic");
//                List<List<Double>> rticGeoJson = new ArrayList<>();
//                //拼接rtic
//                for (DBObject road_rtic : road_rtic_list) {
//                    String rtic_coordinate_seq = (String) road_rtic.get("rtic_coordinate_seq");
//                    String[] seq_split = rtic_coordinate_seq.split(",");
//                    List<Double> coordinate = new ArrayList<>();
//                    int index = 0;
//                    for (int j = 0; j < seq_split.length; j++) {
//                        if (index < 2) {
//                            coordinate.add(Double.valueOf(seq_split[j]));
//                            index += 1;
//                        } else {
//                            coordinate.add(Double.valueOf(seq_split[j]));
//                            index += 1;
//                        }
//                        if (coordinate.size() == 2) {
//                            rticGeoJson.add(coordinate);
//                            index = 0;
//                            coordinate = new ArrayList<>();
//                        }
//                    }
//                }
//
//                JSONObject geoJson = new JSONObject();
//                geoJson.put("type", "LineString");
//                geoJson.put("coordinates", rticGeoJson);
//
//                JSONObject resultObj = new JSONObject();
//                resultObj.put("roadName", roadName);
//                resultObj.put("roadStart", roadStart);
//                resultObj.put("roadEnd", roadEnd);
//                resultObj.put("roadId", roadId);
//                resultObj.put("counts", counts);
//                resultObj.put("geometry", geoJson);
//                resultList.add(resultObj);
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            return resultList.toString();
//        }
    }

    @Override
    public String getCongestionRoadTime(String congestionRoadTimeUrl) {
        return sendGet(congestionRoadTimeUrl);
    }

    @Override
    public String getTeamRoadIndexRT(String teamRoadIndexRTUrl) {
        return sendGet(teamRoadIndexRTUrl);
    }

    @Override
    public JSONObject getTrifficIndexRTByTime(Date time) {
        JSONObject resultObj = new JSONObject();
        DBCollection col = mongoTemplate.getCollection("road_index_history");
        BasicDBObject query = new BasicDBObject();
        query.append("update_time", DateUtils.DateFormatUnit.DATE_AND_MIN.getDateStr(time));
        DBObject indexInfo = col.findOne(query);
        if (indexInfo != null) {
            resultObj.put("dccode", indexInfo.get("dccode"));
            resultObj.put("avgspeed", indexInfo.get("avgspeed"));
            resultObj.put("update_time", indexInfo.get("update_time"));
            resultObj.put("latlon", indexInfo.get("latlon"));
            resultObj.put("dcname", indexInfo.get("dcname"));
            resultObj.put("c_index", indexInfo.get("c_index"));
        }
        return resultObj;
    }

    @Override
    public List<JSONObject> getTrifficIndexRTByTimeFrame(Date todayStartTime, Date todayEndTime) {
        List<JSONObject> resultList = new ArrayList<>();
        DBCollection col = mongoTemplate.getCollection("road_index_history");
        BasicDBObject cond = new BasicDBObject();
        cond.put("$gte", DateUtils.DateFormatUnit.DATE_AND_MIN.getDateStr(todayStartTime));
        cond.put("$lt", DateUtils.DateFormatUnit.DATE_AND_MIN.getDateStr(todayEndTime));
        BasicDBObject query = new BasicDBObject();
        query.put("update_time", cond);
        DBCursor dbObjects = col.find(query);
        while (dbObjects.hasNext()) {
            DBObject indexInfo = dbObjects.next();
            JSONObject resultObj = new JSONObject();
            if (indexInfo != null) {
                resultObj.put("dccode", indexInfo.get("dccode"));
                resultObj.put("avgspeed", indexInfo.get("avgspeed"));
                resultObj.put("update_time", indexInfo.get("update_time"));
                resultObj.put("latlon", indexInfo.get("latlon"));
                resultObj.put("dcname", indexInfo.get("dcname"));
                resultObj.put("c_index", indexInfo.get("c_index"));
            }
            resultList.add(resultObj);
        }
        return resultList;
    }

    private void closeOracle(ResultSet result, PreparedStatement pre, Connection con) {
        try {
            // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
            // 注意关闭的顺序，最后使用的最先关闭
            if (result != null)
                result.close();
            if (pre != null)
                pre.close();
            if (con != null)
                con.close();
            out.println("数据库连接已关闭！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<JSONObject> getAccidentMonitoringByTimeFrame(Date todayStartTime, Date todayEndTime) {
        List<JSONObject> resultList = new ArrayList<>();
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null;// 创建一个结果集对象
        try {
            con = jdbcTemplate.getDataSource().getConnection();
            String sql = "select to_char(bjsj, 'yyyy/mm/dd hh24') bjtime, sum(1) counts from erg_alarm where jqlx = '01' and" +
                    " bjsj >= to_date(?, 'yyyy/mm/dd hh24:mi:ss') and bjsj <= to_date(?, 'yyyy/mm/dd hh24:mi:ss')" +
                    " group by to_char(bjsj, 'yyyy/mm/dd hh24') order by to_char(bjsj, 'yyyy/mm/dd hh24')";// 预编译语句，“？”代表参数
            pre = con.prepareStatement(sql);// 实例化预编译语句
            pre.setString(1, DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayStartTime));// 设置参数，前面的1表示参数的索引，而不是表中列名的索引
            pre.setString(2, DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayEndTime));
            result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
            while (result.next()) {
                JSONObject json = new JSONObject();
                json.put("time", result.getString("bjtime"));
                json.put("count", Integer.parseInt(result.getString("counts")));
                resultList.add(json);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeOracle(result, pre, con);
        }
        return resultList;
    }

    @Override
    public int getAccidentStatisticsCountByTimeFrame(Date todayStartTime, Date todayEndTime) {
        int count = 0;
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null;// 创建一个结果集对象
        try {
            con = jdbcTemplate.getDataSource().getConnection();
            String sql = "select count(1) from erg_alarm where jqlx = '01' and bjsj >= to_date(?, 'yyyy/mm/dd hh24:mi:ss')" +
                    " and bjsj <= to_date(?, 'yyyy/mm/dd hh24:mi:ss')";// 预编译语句，“？”代表参数
            pre = con.prepareStatement(sql);// 实例化预编译语句
            pre.setString(1, DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayStartTime));// 设置参数，前面的1表示参数的索引，而不是表中列名的索引
            pre.setString(2, DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayEndTime));
            result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
            result.next();
            count = result.getInt(1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeOracle(result, pre, con);
        }
        return count;
    }

    @Override
    public int getJamStatisticsCountByTimeFrame(Date todayStartTime, Date todayEndTime) {
        int count = 0;
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null;// 创建一个结果集对象
        try {
            con = jdbcTemplate.getDataSource().getConnection();
            String sql = "select count(1) from erg_alarm where jqlx='02' and jqzlx='0203' and jqzlxfl='0301' and bjsj >= to_date(?, 'yyyy/mm/dd hh24:mi:ss')" +
                    " and bjsj <= to_date(?, 'yyyy/mm/dd hh24:mi:ss')";// 预编译语句，“？”代表参数
            pre = con.prepareStatement(sql);// 实例化预编译语句
            pre.setString(1, DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayStartTime));// 设置参数，前面的1表示参数的索引，而不是表中列名的索引
            pre.setString(2, DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayEndTime));
            result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
            result.next();
            count = result.getInt(1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeOracle(result, pre, con);
        }
        return count;
    }

    @Override
    public int getConstructionCountByTimeFrame(Date todayEndTime) {
        int count = 0;
        SqlRowSet rs = null;
        /*try {
            DBCollection col = mongoTemplate.getCollection("construction_occupation");
            BasicDBObject cond = new BasicDBObject();
            cond.put("$gte", DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayStartTime));
            BasicDBObject query = new BasicDBObject();
            query.put("end_time", cond);
            count = col.find(query).count();
        } catch (Exception e) {
            e.printStackTrace();
        }*/
        String sql = "select count(*) from rod_block t where t.sjlx='A' and  t.yjhfsj>= to_date(?,'yyyy/MM/dd hh24:mi:ss') ";
        rs = jdbcTemplate.queryForRowSet(sql,DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayEndTime));
        rs.next();
        count = rs.getInt(1);
        return count;
    }

    @Override
    public int getControlCountByTimeFrame(Date todayEndTime) {
        int count = 0;
        SqlRowSet rs = null;
        /*try {
            DBCollection col = mongoTemplate.getCollection("traffic_control");
            BasicDBObject query = new BasicDBObject();
            BasicDBList values = new BasicDBList();
            values.add(new BasicDBObject("end_time", new BasicDBObject("$gte", DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayStartTime))));
            values.add(new BasicDBObject("end_time", new BasicDBObject("$gte", DateUtils.DateFormatUnit.DATE.getDateStr(todayStartTime))));
            query.put("$or", values);
            count = col.find(query).count();
        } catch (Exception e) {
            e.printStackTrace();
        }*/
        String sql = "select count(*) from rod_block t where t.sjlx='B' and  t.yjhfsj>= to_date(?,'yyyy/MM/dd hh24:mi:ss')";
        rs = jdbcTemplate.queryForRowSet(sql,DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayEndTime));
        rs.next();
        count = rs.getInt(1);
        return count;
    }

    @Override
    public List<JSONObject> getTrifficAlarmInfo(Date todayStartTime, Date todayEndTime, String areaId, String alarmType) {
        List<JSONObject> resultList = new ArrayList<>();
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null;// 创建一个结果集对象
        try {
            con = jdbcTemplate.getDataSource().getConnection();
            String sql = "select a.jqbh jqbh,a.czbm czbm,a.czsj czsj,b.name bmmc,c.dlmc dlmc,c.szdd szdd,c.wd lat,c.jd lon,c.bjlx bjlx,c.bjlxmc bjlxmc,d.jqlx jqlx,d.jqzlx jqzlx,d.bjnr bjnr,d.LCZT lczt" +
                    " from erg_alarm_handle a left join s_office b on a.czbm = b.id left join erg_alarm_case c on c.jqbh = a.jqbh left join erg_alarm d on a.jqbh = d.jqbh where a.czsj >= to_date(?, 'yyyy/mm/dd hh24:mi:ss')" +
                    " and a.czsj <= to_date(?, 'yyyy/mm/dd hh24:mi:ss')";
            if (!StringUtils.isEmpty(areaId)) {
                sql += " and a.czbm= ?";
            }
            if (!StringUtils.isEmpty(alarmType)) {
                if (alarmType.equals("0301")){
                    sql += " and d.jqzlxfl=?";
                }else {
                    sql += " and d.jqzlx=?";
                }
            }
            sql += " order by a.czsj desc";// 预编译语句，“？”代表参数
            pre = con.prepareStatement(sql);// 实例化预编译语句
            pre.setString(1, DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayStartTime));// 设置参数，前面的1表示参数的索引，而不是表中列名的索引
            pre.setString(2, DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayEndTime));
            if (!StringUtils.isEmpty(areaId)) {
                pre.setString(3, areaId);
            }
            if (!StringUtils.isEmpty(areaId) && !StringUtils.isEmpty(alarmType)) {
                pre.setString(4, alarmType);
            } else if (StringUtils.isEmpty(areaId) && !StringUtils.isEmpty(alarmType)) {
                pre.setString(3, alarmType);
            }
            result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
            while (result.next()) {
                JSONObject json = new JSONObject();
                json.put("jqbh", result.getString("jqbh"));
                json.put("czbm", result.getString("czbm"));
                json.put("czsj", result.getString("czsj"));
                json.put("bmmc", result.getString("bmmc"));
                json.put("dlmc", result.getString("dlmc"));
                json.put("szdd", result.getString("szdd"));
                json.put("lat", result.getString("lat"));
                json.put("lon", result.getString("lon"));
                json.put("bjlx", result.getString("bjlx"));
                json.put("bjlxmc", result.getString("bjlxmc"));
                json.put("jqlx", result.getString("jqlx"));
                json.put("jqzlx", result.getString("jqzlx"));
                json.put("bjnr", result.getString("bjnr"));
                String lczt = result.getString("lczt");
                if (lczt==null){
                    lczt="1";
                }
                if (lczt.equals("1")){//已接警
                    json.put("lczt","0");
                }else if (lczt.equals("9")){//已处理完成
                    json.put("lczt","2");
                }else {//正在处理
                    json.put("lczt","1");
                }
                resultList.add(json);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeOracle(result, pre, con);
        }
        return resultList;
    }

    @Override
    public int getTrifficAlarmCount(String areaId, Date todayStartTime, Date todayEndTime, String alarmType) {
        int count = 0;
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null;// 创建一个结果集对象
        try {
            con = jdbcTemplate.getDataSource().getConnection();
            String sql = "select count(1) from erg_alarm_handle a left join erg_alarm b on a.jqbh=b.jqbh where a.czsj >= to_date(?, 'yyyy/mm/dd hh24:mi:ss')" +
                    " and a.czsj <= to_date(?, 'yyyy/mm/dd hh24:mi:ss')";// 预编译语句，“？”代表参数
            if (!StringUtils.isEmpty(alarmType)) {
                if (alarmType.equals("0301")){
                    sql += " and b.jqzlxfl=?";
                }else {
                    sql += " and b.jqzlx=?";
                }
            }
            if (!StringUtils.isEmpty(areaId)) {
                sql += " and a.czbm=" + areaId;
            }
            pre = con.prepareStatement(sql);// 实例化预编译语句
            pre.setString(1, DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayStartTime));// 设置参数，前面的1表示参数的索引，而不是表中列名的索引
            pre.setString(2, DateUtils.DateFormatUnit.DATE_TIME.getDateStr(todayEndTime));
            if (!StringUtils.isEmpty(alarmType)) {
                pre.setString(3, alarmType);
            }
            result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
            result.next();
            count = result.getInt(1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeOracle(result, pre, con);
        }
        return count;
    }

    @Override
    public List<JSONObject> getCongestionRoadRank1(String congestionRoadRankUrl) {
        GeometryFactory geometryFactory = new GeometryFactory();
        List<JSONObject> resultList = new ArrayList<>();
        try {
            DBCollection col = mongoTemplate.getCollection("roadinfo");

            String result = sendGet(congestionRoadRankUrl);
            JSONArray jsonArray = (JSONArray) JSONArray.parse(result);

            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = (JSONObject) jsonArray.get(i);
                String roadId = jsonObject.getString("roadId");
                String counts = jsonObject.getString("counts");

                DBObject roadInfo = col.findOne(new BasicDBObject("road_id", roadId));
                String roadName = (String) roadInfo.get("road_name");
                String roadStart = (String) roadInfo.get("road_start");
                String roadEnd = (String) roadInfo.get("road_end");

                List<DBObject> road_rtic_list = (List<DBObject>) roadInfo.get("road_rtic");
                List<List<Double>> coordinates = new ArrayList<>();
                //拼接rtic
                for (DBObject road_rtic : road_rtic_list) {
                    String rtic_coordinate_seq = (String) road_rtic.get("rtic_coordinate_seq");
                    String[] seq_split = rtic_coordinate_seq.split(",");
                    List<Double> coordinate = new ArrayList<>();
                    int index = 0;
                    for (int j = 0; j < seq_split.length; j++) {
                        if (index < 2) {
                            coordinate.add(Double.valueOf(seq_split[j]));
                            index += 1;
                        } else {
                            coordinate.add(Double.valueOf(seq_split[j]));
                            index += 1;
                        }
                        if (coordinate.size() == 2) {
                            coordinates.add(coordinate);
                            index = 0;
                            coordinate = new ArrayList<>();
                        }
                    }
                }

                JSONObject geoJson = new JSONObject();
                geoJson.put("type", "LineString");
                geoJson.put("coordinates", coordinates);

                Coordinate[] coords = new Coordinate[coordinates.size()];
                for (int j = 0; j < coordinates.size(); j++) {
                    List<Double> coorObj = coordinates.get(j);
                    double lon = coorObj.get(0);
                    double lat = coorObj.get(1);
                    Coordinate coord = new Coordinate();
                    coord.x = lon;
                    coord.y = lat;
                    coords[j] = coord;
                }
                LineString lineString = geometryFactory.createLineString(coords);
                Point centroid = lineString.getCentroid();
                double x = centroid.getX();
                double y = centroid.getY();
                double[] p = new double[2];
                p[0] = x;
                p[1] = y;

                JSONObject resultObj = new JSONObject();
                resultObj.put("roadName", roadName);
                resultObj.put("roadStart", roadStart);
                resultObj.put("roadEnd", roadEnd);
                resultObj.put("roadId", roadId);
                resultObj.put("counts", counts);
                resultObj.put("centroid", p);

                resultList.add(resultObj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return resultList;
        }
    }

    @Override
    public List<String> getRoadType(String roadType) {
        List<String> rticIdList = new ArrayList<>();
        DBCollection col = mongoTemplate.getCollection("roadrtic");
        BasicDBObject condition = new BasicDBObject();
        if (roadType.equals("G")) {
            condition.append("road_type", "G");
        } else if (roadType.equals("S")) {
            condition.append("road_type", "S");
        } else if (roadType.equals("X")) {
            condition.append("road_type", "X");
        }
        DBCursor cursor = col.find(condition, new BasicDBObject("rtic_id", 1));
        while (cursor.hasNext()) {
            DBObject obj = cursor.next();
            String rticId = (String) obj.get("rtic_id");
            rticIdList.add(rticId);
        }
        return rticIdList;
    }

    @Override
    public String getDegreeOfCongestion(String roadType) {
        return null;
    }

    @Override
    public List<String> getRticIdByRoadId(List<String> roadIdList) {
        List<String> rticIdList = new ArrayList<>();
        DBCollection col = mongoTemplate.getCollection("roadinfo");
        DBCursor cursor = col.find(new BasicDBObject("road_id", new BasicDBObject("$in", roadIdList)));
        while (cursor.hasNext()) {
            DBObject obj = cursor.next();
            List<DBObject> road_rtic = (List<DBObject>) obj.get("road_rtic");
            for (DBObject dbObj : road_rtic) {
                String rticId = (String) dbObj.get("rtic_id");
                rticIdList.add(rticId);
            }
        }
        return rticIdList;
    }

    @Override
    public Double getOutageRticLengthByRticId(List<String> rticIdList) {
        BigDecimal bg = null;
        double outageRticLength = 0;
        try {
            DBCollection col = mongoTemplate.getCollection("traffic_status");
            BasicDBObject condition = new BasicDBObject();
            condition.append("rtic_id", new BasicDBObject("$in", rticIdList));
            condition.append("level", 17);
            DBCursor cursor = col.find(condition, new BasicDBObject("geometry", 1));
            while (cursor.hasNext()) {
                try {
                    DBObject obj = cursor.next();
                    String wkt = (String) obj.get("geometry");
                    LineString lineString = (LineString) wktReader.read(wkt);
                    double length = lineString.getLength();
                    bg = new BigDecimal(length).setScale(2, RoundingMode.UP);
//                System.out.println(bg.doubleValue());
                    outageRticLength += bg.doubleValue();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return outageRticLength;
        }
    }

    @Override
    public List<String> getRticIdCongestionByStatus(int status) {
        List<String> rticIdList = new ArrayList<>();
        try {
            DBCollection col = mongoTemplate.getCollection("traffic_status");
            BasicDBObject condition = new BasicDBObject();
            condition.append("status", 3);
            condition.append("level", 17);
            DBCursor cursor = col.find(condition, new BasicDBObject("rtic_id", 1));
            while (cursor.hasNext()) {
                try {
                    DBObject obj = cursor.next();
                    String rticId = (String) obj.get("rtic_id");
                    rticIdList.add(rticId);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return rticIdList;
        }
    }

    @Override
    public Double getCongestionRticTotalLength(List<String> rticIdList) {
        BigDecimal bg = null;
        double congestionRticLength = 0;
        try {
            DBCollection col = mongoTemplate.getCollection("traffic_status");
            BasicDBObject condition = new BasicDBObject();
            condition.append("rtic_id", new BasicDBObject("$in", rticIdList));
            condition.append("level", 17);
            DBCursor cursor = col.find(condition, new BasicDBObject("geometry", 1));
            while (cursor.hasNext()) {
                try {
                    DBObject obj = cursor.next();
                    String wkt = (String) obj.get("geometry");
                    LineString lineString = (LineString) wktReader.read(wkt);
                    double length = lineString.getLength();
                    bg = new BigDecimal(length).setScale(2, RoundingMode.UP);
//                System.out.println(bg.doubleValue());
                    congestionRticLength += bg.doubleValue();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return congestionRticLength;
        }
    }

    @Override
    public String getKakouCarStatis(String startDate, String endDate, List<String> devIdList) {
        JSONObject geoJson = new JSONObject();
        geoJson.put("type", "FeatureCollection");
        Map<String, JSONObject> map = new HashMap<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = jdbcTemplate.getDataSource().getConnection();
            /*String sql = "SELECT t.kkbh, t.c, b.x, b.y, b.dev_name FROM (" +
                        "SELECT a.kkbh, count(a.kkbh) AS c FROM TFC_PASS a " +
                        "GROUP BY a.kkbh ORDER BY a.kkbh) t " +
                        "LEFT JOIN G_ITDEVICES b ON t.kkbh=b.dev_id";*/
            String sql = "SELECT t.kkbh, t.c, b.x, b.y, b.dev_name FROM (SELECT a.kkbh, count(a.kkbh) AS c FROM tfc_pass_temp a WHERE a.kkbh in" +
                    " (" + org.apache.commons.lang.StringUtils.repeat("?", ",", devIdList.size()) + ") AND a.gcsj" +
                    " >= to_date(?, 'yyyy/mm/dd hh24:mi:ss') and a.gcsj <= to_date(?, 'yyyy/mm/dd hh24:mi:ss') GROUP BY a.kkbh ORDER BY a.kkbh) t" +
                    " LEFT JOIN G_ITDEVICES b ON t.kkbh = b.dev_id";

            ps = con.prepareStatement(sql);// 实例化预编译语句
            int i = 1;
            for (String id : devIdList) {
                ps.setString(i, id);
                i++;
            }
            ps.setString(i, startDate);// 设置参数，前面的1表示参数的索引，而不是表中列名的索引
            ps.setString(++i, endDate);
            rs = ps.executeQuery();// 执行查询，注意括号中不需要再加参数
            while (rs.next()) {
                String deviceId = rs.getString(1);
                int count = rs.getInt(2);
                double lon = rs.getDouble(3);
                double lat = rs.getDouble(4);
                String devName = rs.getString(5);
                String[] dName = devName.split("-");
                String deviceName = dName[0] + "-" + dName[1] + "-" + dName[2] + "-" + dName[3];

                JSONObject feature = new JSONObject();
                JSONObject properties = new JSONObject();
                properties.put("deviceId", deviceId);
                properties.put("deviceName", deviceName);

                JSONObject geometry = new JSONObject();
                geometry.put("type", "Point");
                List<Double> coordinate = new ArrayList<>();
                coordinate.add(lon);
                coordinate.add(lat);
                geometry.put("coordinates", coordinate);

                feature.put("geometry", geometry);

                if (!map.containsKey(deviceName)) {
                    properties.put("count", count);
                } else {
                    JSONObject jsonObject = map.get(deviceName);
                    JSONObject properties1 = (JSONObject) jsonObject.get("properties");
                    properties.put("count", properties1.getIntValue("count") + count);
                }
                feature.put("properties", properties);
                map.put(deviceName, feature);
            }
            List<JSONObject> features = new ArrayList<>(map.values());
            Collections.sort(features, new ViewDaoImpl.compareFeaList());
            geoJson.put("features", features);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeOracle(rs, ps, con);
        }
        return geoJson.toString();
    }

    /**
     * 比较两个对象的大小，实现Comparator接口
     */
    private class compareFeaList implements Comparator<JSONObject> {
        public int compare(JSONObject c1, JSONObject c2) {
            JSONObject properties1 = (JSONObject) c1.get("properties");
            JSONObject properties2 = (JSONObject) c2.get("properties");
            String distance1 = properties1.getString("count");
            String distance2 = properties2.getString("count");
            //通过比较两个字符串对象来排序。此处可以根据自己的需要写两个对象的具体比较内容
            return distance2.compareTo(distance1);
        }
    }

    @Override
    public List<DBObject> getKakouByMongo(String roadType) {
        List<DBObject> kakouList = new ArrayList<>();
        DBCollection col = mongoTemplate.getCollection("devices_info");
        BasicDBObject query = new BasicDBObject();
        if (!StringUtils.isEmpty(roadType)) {
            query.append("dev_type", roadType);
        }
        DBCursor cursor = col.find(query);
        while (cursor.hasNext()) {
            DBObject kakouObj = cursor.next();
            kakouList.add(kakouObj);
        }
        return kakouList;
    }

    @Override
    public String getKakouCarStatisByMongo(String startDate, String endDate, String roadType) {
        JSONObject geoJson = new JSONObject();
        geoJson.put("type", "FeatureCollection");
        Map<String, JSONObject> map = new HashMap<>();
        DBCollection col = mongoTemplate.getCollection("kakou_data_history");
        BasicDBObject cond = new BasicDBObject();
        cond.put("$gte", startDate);
        cond.put("$lt", endDate);
        BasicDBObject query = new BasicDBObject();
        query.put("update_time", cond);
        if (!StringUtils.isEmpty(roadType)) {
            query.append("dev_type", roadType);
        }
        DBCursor cursor = col.find(query, new BasicDBObject("_id", 0));
        while (cursor.hasNext()) {
            DBObject kakouObj = cursor.next();
            List<DBObject> featureList = (List<DBObject>) kakouObj.get("features");
            if (featureList != null) {
                for (DBObject featureDb : featureList) {
                    JSONObject feature = new JSONObject();
                    feature.put("geometry", featureDb.get("geometry"));
                    DBObject proper = (DBObject) featureDb.get("properties");
                    JSONObject properties = new JSONObject();
                    String deviceId = (String) proper.get("deviceId");
                    String deviceName = (String) proper.get("deviceName");
                    properties.put("deviceId", deviceId);
                    properties.put("deviceName", deviceName);
                    if (!map.containsKey(deviceName)) {
                        properties.put("count", proper.get("count"));
                    } else {
                        JSONObject jsonObject = map.get(deviceName);
                        JSONObject properties1 = (JSONObject) jsonObject.get("properties");
                        properties.put("count", properties1.getIntValue("count") + (int) proper.get("count"));
                    }
                    feature.put("properties", properties);
                    map.put(deviceName, feature);
                }
            }
        }
        List<JSONObject> features = new ArrayList<>(map.values());
        Collections.sort(features, new ViewDaoImpl.compareFeaList());
        geoJson.put("features", features);
        return geoJson.toJSONString();
    }

    @Override
    public List<DBObject> getExponentRegionByType(String regionType) {
        List<DBObject> exponentRegionList = new ArrayList<>();
        DBCollection col = mongoTemplate.getCollection("exponent_region");
        BasicDBObject query = new BasicDBObject();
        query.append("region_type", regionType);
        DBCursor cursor = col.find(query);
        while (cursor.hasNext()) {
            DBObject exponentRegionObj = cursor.next();
            exponentRegionList.add(exponentRegionObj);
        }
        return exponentRegionList;
    }

    @Override
    public int queryCongestionRticTotalByRegion(List<String> rticIdList) {
        DBCollection col = mongoTemplate.getCollection(prop.getString("trafficStatus"));

        BasicDBObject query = new BasicDBObject();
        query.append("rtic_id", new BasicDBObject("$in", rticIdList));
        query.append("level", 17);
        List<Integer> statusList = new ArrayList<>();
        statusList.add(2);
        statusList.add(3);
        query.append("status", new BasicDBObject("$in", statusList));
        int congestionRticTotal = col.find(query).count();
        return congestionRticTotal;
    }

    /**
     * 根据缩放等级 + 道路功能等级，过滤查询道路数据
     *
     * @param z
     * @return DBObject filter
     */
    public DBObject getFilter(int z) {
        DBObject filter = new BasicDBObject();
        List<Integer> fcList = new ArrayList<>();
        if (z <= 9) {
            fcList.add(1);
            fcList.add(2);
        } else if (z > 9 && z < 13) {
            fcList.add(1);
            fcList.add(2);
            fcList.add(3);
            fcList.add(4);
        } else if (z >= 13 && z <= 17) {
            fcList.add(1);
            fcList.add(2);
            fcList.add(3);
            fcList.add(4);
            fcList.add(5);
        }
        if (fcList.size() > 0) {
            filter.put("$in", fcList);
            return filter;
        } else {
            return null;
        }
    }


    /**
     * 根据缩放等级 + rtic kind等级，过滤查询道路数据
     *
     * @param z
     * @return
     */
    public DBObject getRticKindFilter(int z) {
        DBObject filter = new BasicDBObject();
        List<Integer> fcList = new ArrayList<>();
        if (z >= 10 && z <= 12) {
//            for (int i = 10; i <= 13; i++) {
//                fcList.add(i);
//            }
            fcList.add(13);
        } else if (z > 12 && z <= 15) {
//            for (int i = 14; i <= 15; i++) {
//                fcList.add(i);
//            }
            fcList.add(15);
        } else {
//            for (int i = 16; i <= 17; i++) {
//                fcList.add(i);
//            }
            fcList.add(17);
        }
        if (fcList.size() > 0) {
            filter.put("$in", fcList);
            return filter;
        } else {
            return null;
        }
    }


    public String sendGet4Pattern(String url) {

        String result = "";
        BufferedReader in = null;
        URL realUrl = null;
        try {
            String urlNameString = url;
            realUrl = new URL(urlNameString);
            out.println(realUrl);
            URLConnection connection = realUrl.openConnection();
            connection.setRequestProperty("connection", "close");
            connection.connect();
            in = new BufferedReader(new InputStreamReader(
                    connection.getInputStream(), "UTF-8"));
            String line;
            while ((line = in.readLine()) != null) {
                result += line + "|";
            }
            result = result.substring(0, result.length() - 1);
        } catch (Exception e) {
            out.println("发送GET请求出现异常！" + e);
            e.printStackTrace();
            try {
                if (in != null) {
                    in.close();
                }
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }
        // 使用finally块来关闭输入流
        finally {
            try {
                if (in != null) {
                    in.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }


    @Override
    public List<String> getKakouCongestion(String jcID) {

        SqlRowSet resultSet = null;
        List<String> list = new ArrayList<>();
        try {
            String sql = "select dmid from CTL_TRAFFIC_DM where GLBM=?";
            resultSet = jdbcTemplate.queryForRowSet(sql,jcID);
            while (resultSet.next()){
                String dmid = resultSet.getString("dmid");
                list.add(dmid);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    @Override
    public JSONObject getKakouCongestionAll(List<String> timeSet) {

        SqlRowSet resultSet = null;
        String timetep = new SimpleDateFormat("yyyyMM").format(new Date());
        /*String sql = "select c.xjbh ,c.xjll,c.xjmc,d.GIS_X,d.GIS_Y,c.tjsd " +
                "from CTL_XJLL_MINUTE_HISTORY_"+timetep+" c ,CTL_TRAFFIC_DM d " +
                "where d.dmid=c.xjbh  " +
                "and c.tjsd =?";*/
        String sql =  "select c.xjbh ,c.xjll,c.xjmc,c.tjsd from CTL_XJLL_MINUTE_HISTORY_"+timetep+" c " +
                "  where  c.tjsd >= ? and c.tjsd<= ?";
        resultSet = jdbcTemplate.queryForRowSet(sql,timeSet.get(timeSet.size()-1),timeSet.get(0));
        JSONObject jsonObject = new JSONObject();
        Set<String> strings = new HashSet<>();
        JSONObject jsonObjectHashMap = new JSONObject();
        while (resultSet.next()){
            JSONObject singlekkjson = new JSONObject();
            String kkbh = resultSet.getString("xjbh");
            String tjsd = resultSet.getString("tjsd");
            singlekkjson.put("kkbh",kkbh);//卡口编号
            singlekkjson.put("kkll",resultSet.getInt("xjll"));//卡口流量
            singlekkjson.put("kkmc",resultSet.getString("xjmc"));//卡口名称
            singlekkjson.put("tjsd",tjsd);
            jsonObjectHashMap.put(tjsd+","+kkbh,singlekkjson);
            strings.add(kkbh);
        }
        jsonObject.put("flowMap",jsonObjectHashMap);
        jsonObject.put("kkbhSet",strings);
        return jsonObject;
  }

    @Override
    public List<String> getMainRoadList() {
        SqlRowSet resultSet = null;
        List<String> mainroadnames = new ArrayList<>();
        String sql = "select * from road_citymain";//查询所有城市主干道
        resultSet = jdbcTemplate.queryForRowSet(sql);
        while (resultSet.next()) {
            String mainroadname = resultSet.getString("mainroadname");
            mainroadnames.add(mainroadname);
        }
        return mainroadnames;
    }


    /**
     * 根据时间和卡口编号集合查询出卡口流量的历史峰值
     * @param timeSet
     * @param kkbhSet
     * @return
     */
    public JSONObject getHisKakoullByBH(List<String> timeSet, Set<String> kkbhSet){

        DBCollection correspondingCol = mongoTemplate.getCollection("kakouFlow");
        BasicDBList dbList = new BasicDBList();
        BasicDBList dbList2 = new BasicDBList();
        Iterator<String> iterator = kkbhSet.iterator();
        while (iterator.hasNext()){
            String next = iterator.next();
            dbList.add(next);
        }
        for (String time : timeSet) {
            dbList2.add(time.split(" ")[1]);
        }
        JSONObject stringJSONObject = new JSONObject();
        String dateType = getDateTypeByDay();
        DBObject basicDBObject = new BasicDBObject("$in",dbList);
        DBObject basicDBObject2 = new BasicDBObject("$in",dbList2);
        BasicDBObject queryDBObject = new BasicDBObject();
        queryDBObject.put("time",basicDBObject2);
        queryDBObject.put("kkbh",basicDBObject);
        queryDBObject.put("timeType","Minute");
        queryDBObject.put("dateType",dateType);
        DBCursor cursor = correspondingCol.find(queryDBObject);
        try {
            while (cursor.hasNext()) {
                DBObject rticObj = cursor.next();
                JSONObject json = new JSONObject();
                int maxkkll = (int) rticObj.get("maxKkll");
                int avgKkll = (int) rticObj.get("avgKkll");
                String  kkbh = (String) rticObj.get("kkbh");
                String  time = (String) rticObj.get("time");
                json.put("maxKkll",maxkkll);
                json.put("avgKkll",avgKkll);
                stringJSONObject.put(kkbh+time,json);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return stringJSONObject;
    }

    @Override
    public JSONObject getDMCoorinates() {
        SqlRowSet rs = null;
        String sql = "select d.GIS_X,d.GIS_Y,d.dmid from CTL_TRAFFIC_DM d ";
        rs = jdbcTemplate.queryForRowSet(sql);
        JSONObject json = new JSONObject();
        while (rs.next()){
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("longitude",rs.getString(1));
            jsonObject.put("latitude",rs.getString(2));
            json.put(rs.getString(3),jsonObject);
        }
        return json;
    }


    @Override
    public String getKakouMessage(String time, String kkbh) {
        JSONObject kakouJson = new JSONObject();

        List<String> timeList = new ArrayList<>();
        Set<String> formatTimeSet = new TreeSet<String>();
        Set<String> formatTimeSet2 = new TreeSet<String>();
        Calendar calendar = Calendar.getInstance();//时间戳

        calendar.add(Calendar.MINUTE, -(60 + 5));

        StringBuffer times = new StringBuffer();
        StringBuffer apex = new StringBuffer();
        StringBuffer historicalAverage = new StringBuffer();
        StringBuffer realTimeTraffic = new StringBuffer();
//        /**
//         * 获取前一个小时时间集合，
//         */
        for (int i = 0; i < 13; i++) {
            calendar.add(Calendar.MINUTE, 5);
            String hisminute = DateUtils.DateFormatUnit.DATE_AND_MIN_SEC.getDateStr(DateUtils.getTimeMinuteByDate(calendar.getTime()));
            String hisminute2 = hisminute.split(" ")[1];
            timeList.add(hisminute);
            formatTimeSet.add(hisminute2);
            formatTimeSet2.add(hisminute);
        }
        JSONObject kakouJsonll = getKakouJsonll(kkbh, timeList);
        Map<Object, Object> map = (Map<Object, Object>) kakouJsonll.get("map");
        if (!map.isEmpty()) {
            Iterator<String> setIterator = formatTimeSet2.iterator();
            while (setIterator.hasNext()) {
                String next = setIterator.next();
                if (map.containsKey(next)) {
                    int prementKakouLl = (int) map.get(next);
                    realTimeTraffic.append(prementKakouLl + ",");
                } else {
                    realTimeTraffic.append("0,");
                }
            }
        } else {
            for (int i = 0; i < timeList.size(); i++) {
                realTimeTraffic.append("0,");
            }
        }

        for (int i = 0; i < 12; i++) {
            calendar.add(Calendar.MINUTE, 5);
            String hisminute2 = DateUtils.DateFormatUnit.DATE_AND_MIN_SEC.getDateStr(DateUtils.getTimeMinuteByDate(calendar.getTime())).split(" ")[1];
            formatTimeSet.add(hisminute2);
        }
        String dateType = getDateTypeByDay();
        Map<String, JSONObject> stringJSONObjectMap = timeOutList(formatTimeSet, kkbh ,dateType);
        Iterator<String> iterator = formatTimeSet.iterator();
        while (iterator.hasNext()) {
            String next = iterator.next();
            String[] split = next.split(":");
            times.append(split[0] + split[1] + ",");
            if (stringJSONObjectMap.containsKey(next)) {
                JSONObject hisKakouLlJson = stringJSONObjectMap.get(next);
                int avgKkll = (int) hisKakouLlJson.get("avgKkll");
                int maxKkll = (int) hisKakouLlJson.get("maxKkll");
                apex.append(maxKkll + ",");
                historicalAverage.append(avgKkll + ",");
            } else {
                historicalAverage.append("0,");
                apex.append("0,");
            }
        }
        String apexs = apex.toString();
        String historicalAverages = historicalAverage.toString();
        String realTimeTraffics = realTimeTraffic.toString();

        String[] apexsplit = apexs.split(",");
        String[] historicalAveragessplit = historicalAverages.split(",");
        String[] realTimeTrafficssplit = realTimeTraffics.split(",");

        int maxKakoull = Integer.parseInt(apexsplit[12]);
        int avgKakoull = Integer.parseInt(historicalAveragessplit[12]);
        int kakoull = Integer.parseInt(realTimeTrafficssplit[12]);

        int hisavgKakoull = Integer.parseInt(historicalAveragessplit[11]);
        int hiskakoull = Integer.parseInt(realTimeTrafficssplit[11]);

        int hiskakoull2 = Integer.parseInt(realTimeTrafficssplit[10]);

        if (kakoull>maxKakoull && kakoull>hiskakoull && hiskakoull>hiskakoull2){
            kakouJson.put("warminglevel","1");
        }else if (kakoull>avgKakoull && kakoull>hiskakoull && hiskakoull>hiskakoull2 && hiskakoull>hisavgKakoull){
            kakouJson.put("warminglevel","0");
        }
        kakouJson.put("times", times.toString());
        kakouJson.put("apex", apex.toString());
        kakouJson.put("historicalAverage", historicalAverage.toString());
        kakouJson.put("realTimeTraffic", realTimeTraffic.toString());

        return kakouJson.toString();
    }

    /**
     * 根据时间集合和卡口编号查询实时流量
     * @param kakouId
     * @param times
     * @return
     */
    public JSONObject getKakouJsonll(String kakouId,List<String> times){
        String timetep = new SimpleDateFormat("yyyyMM").format(new Date());
        String sql = "select xjll,tjsd from CTL_XJLL_MINUTE_HISTORY_"+timetep+" where XJBH =? and tjsd IN (?,?,?,?,?,?,?,?,?,?,?,?,?) ORDER  BY tjsd";

        HashMap<Object, Object> hashMap = new HashMap<>();
        TreeSet<String> treeSet = new TreeSet<>();
        JSONObject jsonObject = new JSONObject();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = jdbcTemplate.getDataSource().getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1,kakouId);
            for (int i =0; i < times.size(); i++) {
                statement.setString(i+2,times.get(i));
            }
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                int kkll = resultSet.getInt("xjll");
                String tjsd = resultSet.getString("tjsd");
                hashMap.put(tjsd,kkll);
                treeSet.add(tjsd);
            }
            jsonObject.put("map",hashMap);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeOracle(resultSet,statement,connection);
        }
        return jsonObject;
    }

    /**
     * 根据传入的时间集合和卡口编号查询出卡口历史平均值和历史峰值的集合
     * @param timeSet
     * @param kkbh
     * @return
     */
    public Map<String, JSONObject> timeOutList(Set<String> timeSet, String kkbh ,String dateType){

        DBCollection correspondingCol = mongoTemplate.getCollection("kakouFlow");
        BasicDBList dbList = new BasicDBList();
        Iterator<String> iterator = timeSet.iterator();
        while (iterator.hasNext()){
            String next = iterator.next();
            dbList.add(next);
        }
        DBObject basicDBObject = new BasicDBObject("$in",dbList);
        Map<String, JSONObject> jsonObjectHashMap = new HashMap<>();
        BasicDBObject queryDBObject = new BasicDBObject();
        queryDBObject.put("time",basicDBObject);
        queryDBObject.put("kkbh",kkbh);
        queryDBObject.put("timeType","Minute");
        queryDBObject.put("dateType",dateType);
        DBCursor cursor = correspondingCol.find(queryDBObject);
        try {
            while (cursor.hasNext()) {
                JSONObject json = new JSONObject();
                DBObject rticObj = cursor.next();
                int avgkkll = (int) rticObj.get("avgKkll");
                int maxkkll = (int) rticObj.get("maxKkll");
                String  time = (String) rticObj.get("time");

                json.put("avgKkll",avgkkll);
                json.put("maxKkll",maxkkll);
                jsonObjectHashMap.put(time,json);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return jsonObjectHashMap;
    }




    public String getDateTypeByDay(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        PreparedStatement statement2 = null;
        ResultSet resultSet2 = null;

        String format = new SimpleDateFormat("yyyy/MM/dd").format(new Date());
        List<String> strings = new ArrayList<>();
        List<String> strings2 = new ArrayList<>();
        try {
            connection = jdbcTemplate.getDataSource().getConnection();
            String sql = "select * from N_WORKDAYS ";
            String sql2 = "select * from N_WORKDAYS where CATEGORYNAME='holi' ";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                String tjsd = resultSet.getString("tjsd");
                strings.add(tjsd);
            }
            statement2 = connection.prepareStatement(sql2);
            resultSet2 = statement2.executeQuery();
            while (resultSet2.next()){
                String tjsd = resultSet2.getString("tjsd");
                strings2.add(tjsd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try{
                if (resultSet2 != null){resultSet2.close();}
                if (statement2 != null){statement2.close();}
                if (resultSet != null){resultSet.close();}
                if (statement != null){statement.close();}
                if (connection != null){connection.close();}
            }catch (Exception e){
                e.getStackTrace();
            }
        }

        if (strings2.contains(format)){
            return "holi";
        }else if (strings.contains(format)){
            return "nwork";
        }else {
            return "work";
        }
    }
    /**
     * 从MongoDB中查询筛选条件下的历史平均值和历史峰值
     *
     * @param timeSet  时刻集合
     * @param monthSet 月份集合
     * @param dateType 日期类型
     * @param flowRate 时间频率
     * @return
     */
    public Map<String,Object> getAvgMaxKakouLlByTime(Set<String> timeSet, Set<String> monthSet, String dateType, String flowRate) {
        HashMap<String, Object> stringObjectHashMap = new HashMap<>();
        if (flowRate.equals("5") || flowRate.equals("10")) {
            DBCollection col;
            if (dateType.equals("work")){
                 col = mongoTemplate.getCollection("kakouFlowMinuteWork");
            }else if (dateType.equals("nwork")){
                 col = mongoTemplate.getCollection("kakouFlowMinuteNWork");
            }else {
                 col = mongoTemplate.getCollection("kakouFlowMinuteHoli");
            }

                BasicDBList dbList = new BasicDBList();
                BasicDBList dbList2 = new BasicDBList();
                Iterator<String> iterator2 = timeSet.iterator();
                while (iterator2.hasNext()) {
                    String next2 = iterator2.next();
                    dbList.add(next2);
                }
                Iterator<String> iterator = monthSet.iterator();
                while (iterator.hasNext()){
                    String next = iterator.next();
                    dbList2.add(next);
                }
                DBObject basicDBObject = new BasicDBObject("$in", dbList);
                DBObject basicDBObject2 = new BasicDBObject("$in", dbList2);
                BasicDBObject queryDBObject = new BasicDBObject();
                queryDBObject.append("time", basicDBObject);
                queryDBObject.append("date", basicDBObject2);
                DBCursor cursor = col.find(queryDBObject);
                while (cursor.hasNext()) {
                    DBObject rticObj = cursor.next();
                    JSONObject json = new JSONObject();
                    int avgKkll = (int) rticObj.get("avgKkll");
                    int maxKkll = (int) rticObj.get("maxKkll");
                    String kkbh = (String) rticObj.get("kkbh");
                    String time = (String) rticObj.get("time");
                    String date = (String) rticObj.get("date");
                    json.put("avgkkll", avgKkll);
                    json.put("maxkkll", maxKkll);
                    stringObjectHashMap.put(kkbh + time+ date, json);
                }
        } else if (flowRate.equals("60")) {
                DBCollection col = mongoTemplate.getCollection("kakouFlowHour");
                BasicDBList dbList = new BasicDBList();
                BasicDBList dbList2 = new BasicDBList();
                Iterator<String> iterator2 = timeSet.iterator();
                while (iterator2.hasNext()) {
                    String next2 = iterator2.next();
                    dbList.add(next2);
                }
                Iterator<String> iterator = monthSet.iterator();
                while (iterator.hasNext()){
                    String next = iterator.next();
                    dbList2.add(next);
                }
                DBObject basicDBObject = new BasicDBObject("$in", dbList);
                DBObject basicDBObject2 = new BasicDBObject("$in", dbList2);
                BasicDBObject queryDBObject = new BasicDBObject();
                queryDBObject.put("time", basicDBObject);
                queryDBObject.append("date", basicDBObject2);
                queryDBObject.append("dateType", dateType);
                DBCursor cursor = col.find(queryDBObject);
                while (cursor.hasNext()) {
                    DBObject rticObj = cursor.next();
                    JSONObject json = new JSONObject();
                    int avgKkll = (int) rticObj.get("avgKkll");
                    int maxKkll = (int) rticObj.get("maxKkll");
                    String kkbh = (String) rticObj.get("kkbh");
                    String time = (String) rticObj.get("time");
                    String date = (String) rticObj.get("date");
                    json.put("avgkkll", avgKkll);
                    json.put("maxkkll", maxKkll);
                    stringObjectHashMap.put(kkbh + time + date, json);
                }
        }
        return stringObjectHashMap;
    }

    /**
     * 查询实时流量数据
     *
     * @param timeSet  时间集合
     * @param flowRate 时间频率
     * @return
     */
    public JSONObject getKakoullByTimeSet(Set<String> timeSet, String flowRate) {

        String timetep = new SimpleDateFormat("yyyyMM").format(new Date());

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String sql;
        if (flowRate.equals("60")) {
            sql = "select c.xjbh ,c.xjll,g.dmmc,g.GIS_X,g.GIS_Y,c.tjsd from CTL_XJLL_HOUR_HISTORY_" + timetep + " c ,CTL_TRAFFIC_DM g " +
                    "where g.dmid=c.xjbh and c.xjbh in( select g.dmid FROM CTL_TRAFFIC_DM g ) and c.tjsd IN" +
                    " (" + org.apache.commons.lang.StringUtils.repeat("?", ",", timeSet.size()) + ") ORDER BY c.tjsd,c.xjbh";
        } else {
            sql = "select c.xjbh ,c.xjll,g.dmmc,g.GIS_X,g.GIS_Y,c.tjsd from CTL_XJLL_MINUTE_HISTORY_" + timetep + " c ,CTL_TRAFFIC_DM g " +
                    "where g.dmid=c.xjbh and c.xjbh in( select g.dmid FROM CTL_TRAFFIC_DM g ) and c.tjsd IN " +
                    "(" + org.apache.commons.lang.StringUtils.repeat("?", ",", timeSet.size()) + ") ORDER BY c.tjsd,c.xjbh";
        }
        JSONObject jsonObject = new JSONObject();
        Set<String> strings = new HashSet<>();
        Map<String, JSONObject> jsonObjectHashMap = new HashMap<>();
        try {
            connection = jdbcTemplate.getDataSource().getConnection();
            statement = connection.prepareStatement(sql);
            Iterator<String> iterator = timeSet.iterator();
            int count = 1;
            while (iterator.hasNext()) {
                String next = iterator.next();
                statement.setString(count, next);
                count++;
            }
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                JSONObject singlekkjson = new JSONObject();
                int kkll = resultSet.getInt("xjll");
                String kkbh = resultSet.getString("xjbh");
                String kkmc = resultSet.getString("dmmc");
                double longitude = resultSet.getDouble("GIS_X");//经度
                double latitude = resultSet.getDouble("GIS_Y");//纬度
                String tjsd = resultSet.getString("tjsd");
                singlekkjson.put("kkbh", kkbh);//卡口编号
                singlekkjson.put("kkll", kkll);//卡口流量
                singlekkjson.put("kkmc", kkmc);//卡口名称
                singlekkjson.put("longitude", longitude);//经度
                singlekkjson.put("latitude", latitude);//纬度
                singlekkjson.put("tjsd", tjsd);
                jsonObjectHashMap.put(tjsd + kkbh, singlekkjson);
                strings.add(kkbh);
            }
            jsonObject.put("flowMap", jsonObjectHashMap);
            jsonObject.put("kkbhSet", strings);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeOracle(resultSet,statement,connection);
        }
        return jsonObject;
    }

    /**
     * 查询出符合条件的卡口列表接口
     *
     * @param dateType      日期类型
     * @param flowStartTime 开始日期
     * @param flowEndTime   结束日期
     * @param flowRate      时间频率
     * @param warminglevel  预警级别
     * @return
     */
    @Override
    public String getKakouTabulation(String dateType, String flowStartTime, String flowEndTime, String flowRate, String warminglevel) {

        Calendar calendar1 = Calendar.getInstance();


        HashSet<String> monthSet = new HashSet<>();
        HashSet<String> timeSet = new HashSet<>();
        HashSet<String> hisTimeSet = new HashSet<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        try {
            calendar1.setTime(sdf.parse(flowStartTime));
            calendar1.add(Calendar.MONTH, 1);
            String month = sdf.format(calendar1.getTime());

            monthSet.add(flowStartTime);
            monthSet.add(month);
            monthSet.add(flowEndTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        ArrayList<JSONObject> roadList = new ArrayList<>();
        Map<Object, Object> roadKkbhMap = new HashMap<>();

        Calendar calendar = Calendar.getInstance();
        if (flowRate.equals("60")) {
            String timefor = DateUtils.DateFormatUnit.DATE_AND_MIN_SEC.getDateStr(calendar.getTime());
//            String timefor = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());//当前时间(实际时间）
//            String[] split = timefor.split(":");
            String format;
            String mongoFormat;
            if (timefor.split(":")[1].equals("00")) {
                calendar.add(Calendar.HOUR, -2);
                format = DateUtils.DateFormatUnit.DATE_AND_MIN_SEC.getDateStr(calendar.getTime());
//                format = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());//从oracle中查询实时数据使用的时间
                mongoFormat = format.split(" ")[1];//从MongoDB中查询数据使用的时间
                timeSet.add(format);
                hisTimeSet.add(mongoFormat);
            } else {
                calendar.add(Calendar.HOUR, -1);
                format = new SimpleDateFormat("yyyy/MM/dd HH:00:00").format(calendar.getTime());//从oracle中查询实时数据使用的时间
                mongoFormat = format.split(" ")[1];//从MongoDB中查询数据使用的时间
                timeSet.add(format);
                hisTimeSet.add(mongoFormat);
            }
            calendar.add(Calendar.HOUR, -1);
            String hisFormat = new SimpleDateFormat("yyyy/MM/dd HH:00:00").format(calendar.getTime());//从oracle中查询实时数据使用的时间
            String hismongoFormat = hisFormat.split(" ")[1];//从MongoDB中查询数据使用的时间
            timeSet.add(hisFormat);
            hisTimeSet.add(hismongoFormat);

            calendar.add(Calendar.HOUR, -1);
            String hisFormat2 = new SimpleDateFormat("yyyy/MM/dd HH:00:00").format(calendar.getTime());//从oracle中查询实时数据使用的时间
            timeSet.add(hisFormat2);

            JSONObject kakoullNowJsonObject = getKakoullByTimeSet(timeSet, flowRate);//实时流量对象
            Map<String, Object> avgMaxKakouLlByTime = getAvgMaxKakouLlByTime(hisTimeSet, monthSet, dateType, flowRate);//历史数据

            Set<String> kkbhSet = (Set<String>) kakoullNowJsonObject.get("kkbhSet");
            Map<String, Object> flowMap = (Map<String, Object>) kakoullNowJsonObject.get("flowMap");

            //建立卡口编号与道路名称的关系，为多对一
            HashSet<String> roadSet = new HashSet<>();
            Iterator<String> kkbhiterator = kkbhSet.iterator();
            while (kkbhiterator.hasNext()) {
                String next = kkbhiterator.next();
                if (flowMap != null) {
                    JSONObject kakouJson = (JSONObject) flowMap.get(format + next);
                    if (kakouJson != null) {
                        String kkmc = (String) kakouJson.get("kkmc");
                        roadSet.add(kkmc.split("-")[0]);//道路名称集合
                        roadKkbhMap.put(kkmc.split("-")[0] + next, next);
                    }
                }
            }
            Iterator<String> iterator1 = roadSet.iterator();
            while (iterator1.hasNext()) {
                String next = iterator1.next();
                List<String> strings = new ArrayList<>();
                Iterator<String> iterator = kkbhSet.iterator();
                while (iterator.hasNext()) {
                    String next1 = iterator.next();
                    if (roadKkbhMap.containsKey(next + next1)) {
                        strings.add(next1);
                    }
                }
                roadKkbhMap.put(next, strings);
            }
            //遍历道路名称集合，每个道路名称下对应着一个卡口编号集合，
            // 根据卡口编号和时间得到查询到的历史平均值历史峰值以及实时流量值
            Iterator<String> iterator = roadSet.iterator();
            while (iterator.hasNext()) {
                String next = iterator.next();//道路名
                List<String> kkbhList = (List<String>) roadKkbhMap.get(next);//获取卡口编号集合
                List<JSONObject> kakouList = new ArrayList<>();
                JSONObject roadJson = new JSONObject();
                for (int i = 0; i < kkbhList.size(); i++) {//遍历卡口编号
                    if (flowMap != null && avgMaxKakouLlByTime != null && avgMaxKakouLlByTime.size() > 0) {
                        JSONObject kakouJson = (JSONObject) flowMap.get(format + kkbhList.get(i));
                        JSONObject hisKakouJson = (JSONObject) flowMap.get(hisFormat + kkbhList.get(i));
                        JSONObject hisKakouJson2 = (JSONObject) flowMap.get(hisFormat2 + kkbhList.get(i));
                        if (kakouJson != null && hisKakouJson != null && hisKakouJson2 != null) {
                            List<Integer> maxlist = new ArrayList<>();
                            int sum = 0;
                            int hisSum = 0;
                            int count = 0 ;
                            Iterator<String> iterator2 = monthSet.iterator();
                            while (iterator2.hasNext()){
                                String next1 = iterator2.next();
                                if (avgMaxKakouLlByTime != null && avgMaxKakouLlByTime.containsKey(kkbhList.get(i) + mongoFormat+next1) &&
                                        avgMaxKakouLlByTime.containsKey(kkbhList.get(i) + hismongoFormat + next1)) {
                                    JSONObject jsonObject = (JSONObject) avgMaxKakouLlByTime.get(kkbhList.get(i) + mongoFormat + next1);
                                    JSONObject hisJsonObject = (JSONObject) avgMaxKakouLlByTime.get(kkbhList.get(i) + hismongoFormat+next1);
                                    int avgkkll = (int) jsonObject.get("avgkkll");
                                    int hisAvgkkll = (int) hisJsonObject.get("avgkkll");
                                    int maxkkll = (int) jsonObject.get("maxkkll");
                                    maxlist.add(maxkkll);
                                    sum += avgkkll;
                                    hisSum += hisAvgkkll;
                                    count++;
                                }
                            }
                            if (maxlist.size() > 0) {
                                int realAvgKkll = sum / count;
                                int hisRealAvgKkll = hisSum / count;
                                int realMaxKkll = Collections.max(maxlist);
                                int kkll = (int) kakouJson.get("kkll");
                                int hisKkll = (int) hisKakouJson.get("kkll");
                                int hisKkll2 = (int) hisKakouJson2.get("kkll");
                                if (warminglevel.equals("0")) {
                                    if (kkll > hisKkll && hisKkll > hisKkll2 && kkll > realAvgKkll && hisKkll > hisRealAvgKkll) {
                                        JSONObject json = new JSONObject();
                                        String kkbh = (String) kakouJson.get("kkbh");
                                        String kkmc = (String) kakouJson.get("kkmc");
                                        double longitude = (double) kakouJson.get("longitude");
                                        double latitude = (double) kakouJson.get("latitude");
                                        json.put("kakouName", kkmc);
                                        json.put("kakouBH", kkbh);
                                        List<Double> doubles = new ArrayList<>();
                                        JSONObject geojson = new JSONObject();
                                        doubles.add(longitude);
                                        doubles.add(latitude);
                                        geojson.put("type", "Point");
                                        geojson.put("coordinates", doubles);

                                        JSONObject kakouJsonObject = new JSONObject();
                                        kakouJsonObject.put("properties", json);
                                        kakouJsonObject.put("geometry", geojson);
                                        kakouList.add(kakouJsonObject);
                                    }
                                } else {
                                    if (kkll > realMaxKkll && kkll > hisKkll && hisKkll > hisKkll2) {
                                        JSONObject json = new JSONObject();
                                        String kkbh = (String) kakouJson.get("kkbh");
                                        String kkmc = (String) kakouJson.get("kkmc");
                                        double longitude = (double) kakouJson.get("longitude");
                                        double latitude = (double) kakouJson.get("latitude");
                                        json.put("kakouName", kkmc);
                                        json.put("kakouBH", kkbh);
                                        List<Double> doubles = new ArrayList<>();
                                        JSONObject geojson = new JSONObject();
                                        doubles.add(longitude);
                                        doubles.add(latitude);
                                        geojson.put("type", "Point");
                                        geojson.put("coordinates", doubles);

                                        JSONObject kakouJsonObject = new JSONObject();
                                        kakouJsonObject.put("properties", json);
                                        kakouJsonObject.put("geometry", geojson);
                                        kakouList.add(kakouJsonObject);
                                    }
                                }
                            }
                        }
                    }
                }
                if (kakouList.size() > 0) {
                    roadJson.put("roadName", next);
                    roadJson.put("kakous", kakouList);
                    roadList.add(roadJson);
                }
            }
        } else if (flowRate.equals("5")) {
            KakouTraffic kakouTraffic = new KakouTraffic();
            String timefor = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());//当前时间(实际时间）
            String[] split = timefor.split(":");
            String format;
            String mongoFormat;
            if (Integer.parseInt(split[1]) % 5 == 0) {
                calendar.add(Calendar.MINUTE, -5);
                format = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());//从oracle中查询实时数据使用的时间
                mongoFormat = format.split(" ")[1];//从MongoDB中查询数据使用的时间
                timeSet.add(format);
                hisTimeSet.add(mongoFormat);
            } else {
                format = kakouTraffic.getMinute(timefor);//从oracle中查询实时数据使用的时间
                mongoFormat = format.split(" ")[1];//从MongoDB中查询数据使用的时间
                timeSet.add(format);
                hisTimeSet.add(mongoFormat);
            }
            calendar.add(Calendar.MINUTE, -5);
            String timfor2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());//从oracle中查询实时数据使用的时间
            String hisFormat = kakouTraffic.getMinute(timfor2);
            String hismongoFormat = hisFormat.split(" ")[1];//从MongoDB中查询数据使用的时间
            timeSet.add(hisFormat);
            hisTimeSet.add(hismongoFormat);

            calendar.add(Calendar.MINUTE, -5);
            String timfor3 = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());//从oracle中查询实时数据使用的时间
            String hisFormat2 = kakouTraffic.getMinute(timfor3);
            timeSet.add(hisFormat2);

            JSONObject kakoullNowJsonObject = getKakoullByTimeSet(timeSet, flowRate);//实时流量对象
            Map<String, Object> avgMaxKakouLlByTime = getAvgMaxKakouLlByTime(hisTimeSet, monthSet, dateType, flowRate);//历史数据
            Set<String> kkbhSet = (Set<String>) kakoullNowJsonObject.get("kkbhSet");
            Map<String, Object> flowMap = (Map<String, Object>) kakoullNowJsonObject.get("flowMap");

            //建立卡口编号与道路名称的关系，为多对一
            HashSet<String> roadSet = new HashSet<>();
            Iterator<String> kkbhiterator = kkbhSet.iterator();
            while (kkbhiterator.hasNext()) {
                String next = kkbhiterator.next();
                if (flowMap != null) {
                    JSONObject kakouJson = (JSONObject) flowMap.get(format + next);
                    if (kakouJson != null) {
                        String kkmc = (String) kakouJson.get("kkmc");
                        roadSet.add(kkmc.split("-")[0]);//道路名称集合
                        roadKkbhMap.put(kkmc.split("-")[0] + next, next);
                    }
                }
            }
            Iterator<String> iterator1 = roadSet.iterator();
            while (iterator1.hasNext()) {
                String next = iterator1.next();
                List<String> strings = new ArrayList<>();
                Iterator<String> iterator = kkbhSet.iterator();
                while (iterator.hasNext()) {
                    String next1 = iterator.next();
                    if (roadKkbhMap.containsKey(next + next1)) {
                        strings.add(next1);
                    }
                }
                roadKkbhMap.put(next, strings);
            }

            //遍历道路名称集合，每个道路名称下对应着一个卡口编号集合，
            // 根据卡口编号和时间得到查询到的历史平均值历史峰值以及实时流量值
            Iterator<String> iterator = roadSet.iterator();
            while (iterator.hasNext()) {
                String next = iterator.next();//道路名
                List<String> kkbhList = (List<String>) roadKkbhMap.get(next);//获取卡口编号集合
                List<JSONObject> kakouList = new ArrayList<>();
                JSONObject roadJson = new JSONObject();
                for (int i = 0; i < kkbhList.size(); i++) {//遍历卡口编号
                    if (flowMap != null && avgMaxKakouLlByTime != null && avgMaxKakouLlByTime.size() > 0) {
                        JSONObject kakouJson = (JSONObject) flowMap.get(format + kkbhList.get(i));
                        JSONObject hisKakouJson = (JSONObject) flowMap.get(hisFormat + kkbhList.get(i));
                        JSONObject hisKakouJson2 = (JSONObject) flowMap.get(hisFormat2 + kkbhList.get(i));
                        if (kakouJson != null && hisKakouJson != null && hisKakouJson2 != null) {
                            List<Integer> maxlist = new ArrayList<>();
                            int sum = 0;
                            int hisSum = 0;
                            int count =0;
                            Iterator<String> iterator2 = monthSet.iterator();
                            while (iterator2.hasNext()){
                                String next1 = iterator2.next();
                                if (avgMaxKakouLlByTime != null && avgMaxKakouLlByTime.containsKey(kkbhList.get(i) + mongoFormat+next1) &&
                                        avgMaxKakouLlByTime.containsKey(kkbhList.get(i) + hismongoFormat + next1)) {
                                    JSONObject jsonObject = (JSONObject) avgMaxKakouLlByTime.get(kkbhList.get(i) + mongoFormat + next1);
                                    JSONObject hisJsonObject = (JSONObject) avgMaxKakouLlByTime.get(kkbhList.get(i) + hismongoFormat+next1);
                                    int avgkkll = (int) jsonObject.get("avgkkll");
                                    int hisAvgkkll = (int) hisJsonObject.get("avgkkll");
                                    int maxkkll = (int) jsonObject.get("maxkkll");
                                    maxlist.add(maxkkll);
                                    sum += avgkkll;
                                    hisSum += hisAvgkkll;
                                    count++;
                                }
                            }
                            if (sum != 0 && hisSum != 0 && maxlist.size() > 0) {
                                int realAvgKkll = sum / count;
                                int hisRealAvgKkll = hisSum / count;
                                int realMaxKkll = Collections.max(maxlist);
                                int kkll = (int) kakouJson.get("kkll");
                                int hisKkll = (int) hisKakouJson.get("kkll");
                                int hisKkll2 = (int) hisKakouJson2.get("kkll");
                                if (warminglevel.equals("0")) {
                                    if (kkll > hisKkll && hisKkll > hisKkll2 && kkll > realAvgKkll && hisKkll > hisRealAvgKkll) {
                                        JSONObject json = new JSONObject();
                                        String kkbh = (String) kakouJson.get("kkbh");
                                        String kkmc = (String) kakouJson.get("kkmc");
                                        double longitude = (double) kakouJson.get("longitude");
                                        double latitude = (double) kakouJson.get("latitude");
                                        json.put("kakouName", kkmc);
                                        json.put("kakouBH", kkbh);
                                        List<Double> doubles = new ArrayList<>();
                                        JSONObject geojson = new JSONObject();
                                        doubles.add(longitude);
                                        doubles.add(latitude);
                                        geojson.put("type", "Point");
                                        geojson.put("coordinates", doubles);

                                        JSONObject kakouJsonObject = new JSONObject();
                                        kakouJsonObject.put("properties", json);
                                        kakouJsonObject.put("geometry", geojson);
                                        kakouList.add(kakouJsonObject);
                                    }
                                } else {
                                    if (kkll > realMaxKkll && kkll > hisKkll && hisKkll > hisKkll2) {
                                        JSONObject json = new JSONObject();
                                        String kkbh = (String) kakouJson.get("kkbh");
                                        String kkmc = (String) kakouJson.get("kkmc");
                                        double longitude = (double) kakouJson.get("longitude");
                                        double latitude = (double) kakouJson.get("latitude");
                                        json.put("kakouName", kkmc);
                                        json.put("kakouBH", kkbh);
                                        //                                    json.put("kakouFlowRate", kkll);
                                        List<Double> doubles = new ArrayList<>();
                                        JSONObject geojson = new JSONObject();
                                        doubles.add(longitude);
                                        doubles.add(latitude);
                                        geojson.put("type", "Point");
                                        geojson.put("coordinates", doubles);

                                        JSONObject kakouJsonObject = new JSONObject();
                                        kakouJsonObject.put("properties", json);
                                        kakouJsonObject.put("geometry", geojson);
                                        kakouList.add(kakouJsonObject);
                                    }
                                }
                            }
                        }
                    }
                }
                if (kakouList.size() > 0) {
                    roadJson.put("roadName", next);
                    roadJson.put("kakous", kakouList);
                    roadList.add(roadJson);
                }
            }
        } else if (flowRate.equals("10")) {
            KakouTraffic kakouTraffic = new KakouTraffic();
            String timefor = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());//当前时间(实际时间）
            String[] split = timefor.split(":");
            String format;
            String mongoFormat;
            if (Integer.parseInt(split[1]) % 5 == 0) {
                calendar.add(Calendar.MINUTE, -5);
                format = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());//从oracle中查询实时数据使用的时间
                mongoFormat = format.split(" ")[1];//从MongoDB中查询数据使用的时间
                timeSet.add(format);
                hisTimeSet.add(mongoFormat);
            } else {
                format = kakouTraffic.getMinute(timefor);//从oracle中查询实时数据使用的时间
                mongoFormat = format.split(" ")[1];//从MongoDB中查询数据使用的时间
                timeSet.add(format);
                hisTimeSet.add(mongoFormat);
            }
            calendar.add(Calendar.MINUTE, -5);
            String timfor2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());//从oracle中查询实时数据使用的时间
            String format2 = kakouTraffic.getMinute(timfor2);
            String mongoFormat2 = format2.split(" ")[1];//从MongoDB中查询数据使用的时间
            timeSet.add(format2);
            hisTimeSet.add(mongoFormat2);

            calendar.add(Calendar.MINUTE, -5);
            String timfor3 = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());//从oracle中查询实时数据使用的时间
            String hisFormat = kakouTraffic.getMinute(timfor3);
            String hismongoFormat = hisFormat.split(" ")[1];//从MongoDB中查询数据使用的时间
            timeSet.add(hisFormat);
            hisTimeSet.add(hismongoFormat);

            calendar.add(Calendar.MINUTE, -5);
            String timfor4 = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());//从oracle中查询实时数据使用的时间
            String hisFormat2 = kakouTraffic.getMinute(timfor4);
            String hismongoFormat2 = hisFormat.split(" ")[1];//从MongoDB中查询数据使用的时间
            timeSet.add(hisFormat2);
            hisTimeSet.add(hismongoFormat2);

            calendar.add(Calendar.MINUTE, -5);
            String timfor5 = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());//从oracle中查询实时数据使用的时间
            String hisFormat3 = kakouTraffic.getMinute(timfor5);
            timeSet.add(hisFormat3);

            calendar.add(Calendar.MINUTE, -5);
            String timfor6 = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());//从oracle中查询实时数据使用的时间
            String hisFormat4 = kakouTraffic.getMinute(timfor6);
            timeSet.add(hisFormat4);

            JSONObject kakoullNowJsonObject = getKakoullByTimeSet(timeSet, flowRate);//实时流量对象
            Map<String, Object> avgMaxKakouLlByTime = getAvgMaxKakouLlByTime(hisTimeSet, monthSet, dateType, flowRate);//历史数据
            Set<String> kkbhSet = (Set<String>) kakoullNowJsonObject.get("kkbhSet");
            Map<String, Object> flowMap = (Map<String, Object>) kakoullNowJsonObject.get("flowMap");

            //建立卡口编号与道路名称的关系，为多对一
            HashSet<String> roadSet = new HashSet<>();
            Iterator<String> kkbhiterator = kkbhSet.iterator();
            while (kkbhiterator.hasNext()) {
                String next = kkbhiterator.next();
                if (flowMap != null) {
                    JSONObject kakouJson = (JSONObject) flowMap.get(format + next);
                    if (kakouJson != null) {
                        String kkmc = (String) kakouJson.get("kkmc");
                        roadSet.add(kkmc.split("-")[0]);//道路名称集合
                        roadKkbhMap.put(kkmc.split("-")[0] + next, next);
                    }
                }
            }
            Iterator<String> iterator1 = roadSet.iterator();
            while (iterator1.hasNext()) {
                String next = iterator1.next();
                List<String> strings = new ArrayList<>();
                Iterator<String> iterator = kkbhSet.iterator();
                while (iterator.hasNext()) {
                    String next1 = iterator.next();
                    if (roadKkbhMap.containsKey(next + next1)) {
                        strings.add(next1);
                    }
                }
                roadKkbhMap.put(next, strings);
            }

            //遍历道路名称集合，每个道路名称下对应着一个卡口编号集合，
            // 根据卡口编号和时间得到查询到的历史平均值历史峰值以及实时流量值
            Iterator<String> iterator = roadSet.iterator();
            while (iterator.hasNext()) {
                String next = iterator.next();//道路名
                List<String> kkbhList = (List<String>) roadKkbhMap.get(next);//获取卡口编号集合
                List<JSONObject> kakouList = new ArrayList<>();
                JSONObject roadJson = new JSONObject();
                for (int i = 0; i < kkbhList.size(); i++) {//遍历卡口编号
                    if (flowMap != null && avgMaxKakouLlByTime != null && avgMaxKakouLlByTime.size() > 0) {
                        JSONObject kakouJson = (JSONObject) flowMap.get(format + kkbhList.get(i));
                        JSONObject kakouJson2 = (JSONObject) flowMap.get(format2 + kkbhList.get(i));
                        JSONObject hiskakouJson = (JSONObject) flowMap.get(hisFormat + kkbhList.get(i));
                        JSONObject hiskakouJson2 = (JSONObject) flowMap.get(hisFormat2 + kkbhList.get(i));
                        JSONObject hiskakouJson3 = (JSONObject) flowMap.get(hisFormat3 + kkbhList.get(i));
                        JSONObject hiskakouJson4 = (JSONObject) flowMap.get(hisFormat4 + kkbhList.get(i));
                        if (kakouJson != null && kakouJson2 != null && hiskakouJson != null && hiskakouJson2 != null && hiskakouJson3 != null && hiskakouJson4 != null) {
                            List<Integer> maxlist = new ArrayList<>();
                            int sum = 0;
                            int hisSum = 0;
                            int count =0;
                            Iterator<String> iterator2 = monthSet.iterator();
                            while (iterator2.hasNext()){
                                String next1 = iterator2.next();
                                if (avgMaxKakouLlByTime != null && avgMaxKakouLlByTime.containsKey(kkbhList.get(i) + mongoFormat +next1) &&
                                        avgMaxKakouLlByTime.containsKey(kkbhList.get(i) + mongoFormat2+next1) &&
                                        avgMaxKakouLlByTime.containsKey(kkbhList.get(i) + hismongoFormat+next1) &&
                                        avgMaxKakouLlByTime.containsKey(kkbhList.get(i) + hismongoFormat2+next1)) {

                                    JSONObject jsonObject = (JSONObject) avgMaxKakouLlByTime.get(kkbhList.get(i) + mongoFormat+next1);
                                    JSONObject jsonObject2 = (JSONObject) avgMaxKakouLlByTime.get(kkbhList.get(i) + mongoFormat2+next1);
                                    JSONObject hisJsonObject = (JSONObject) avgMaxKakouLlByTime.get(kkbhList.get(i) + hismongoFormat+next1);
                                    JSONObject hisJsonObject2 = (JSONObject) avgMaxKakouLlByTime.get(kkbhList.get(i) + hismongoFormat2+next1);
                                    int avgkkll = (int) jsonObject.get("avgkkll");
                                    int avgkkll2 = (int) jsonObject2.get("avgkkll");
                                    int hisAvgkkll = (int) hisJsonObject.get("avgkkll");
                                    int hisAvgkkll2 = (int) hisJsonObject2.get("avgkkll");

                                    int maxkkll = (int) jsonObject.get("maxkkll");
                                    int maxkkll2 = (int) jsonObject2.get("maxkkll");

                                    maxlist.add(maxkkll + maxkkll2);

                                    sum += (avgkkll + avgkkll2);
                                    hisSum += (hisAvgkkll + hisAvgkkll2);
                                    count++;
                                }
                            }

                            if (maxlist != null && maxlist.size() > 0) {
                                int realAvgKkll = sum / count;
                                int hisRealAvgKkll = hisSum / count;
                                int realMaxKkll = Collections.max(maxlist);
                                int kkll1 = (int) kakouJson.get("kkll");
                                int kkll12 = (int) kakouJson2.get("kkll");
                                int kkll = kkll1 + kkll12;
                                int hisKkll1 = (int) hiskakouJson.get("kkll");
                                int hisKkll2 = (int) hiskakouJson2.get("kkll");
                                int hisKkll = hisKkll1 + hisKkll2;
                                int hisKkll3 = (int) hiskakouJson3.get("kkll");
                                int hisKkll4 = (int) hiskakouJson4.get("kkll");
                                int hisKkllh = hisKkll3 + hisKkll4;

                                if (warminglevel.equals("0")) {
                                    if (kkll > hisKkll && hisKkll > hisKkllh && kkll > realAvgKkll && hisKkll > hisRealAvgKkll) {
                                        JSONObject json = new JSONObject();
                                        String kkbh = (String) kakouJson.get("kkbh");
                                        String kkmc = (String) kakouJson.get("kkmc");
                                        double longitude = (double) kakouJson.get("longitude");
                                        double latitude = (double) kakouJson.get("latitude");
                                        json.put("kakouName", kkmc);
                                        json.put("kakouBH", kkbh);
                                        //                                    json.put("kakouFlowRate", kkll);
                                        List<Double> doubles = new ArrayList<>();
                                        JSONObject geojson = new JSONObject();
                                        doubles.add(longitude);
                                        doubles.add(latitude);
                                        geojson.put("type", "Point");
                                        geojson.put("coordinates", doubles);

                                        JSONObject kakouJsonObject = new JSONObject();
                                        kakouJsonObject.put("properties", json);
                                        kakouJsonObject.put("geometry", geojson);
                                        kakouList.add(kakouJsonObject);
                                    }
                                } else {
                                    if (kkll > realMaxKkll && kkll > hisKkll && hisKkll > hisKkllh) {
                                        JSONObject json = new JSONObject();
                                        String kkbh = (String) kakouJson.get("kkbh");
                                        String kkmc = (String) kakouJson.get("kkmc");
                                        double longitude = (double) kakouJson.get("longitude");
                                        double latitude = (double) kakouJson.get("latitude");
                                        json.put("kakouName", kkmc);
                                        json.put("kakouBH", kkbh);
                                        //                                    json.put("kakouFlowRate", kkll);
                                        List<Double> doubles = new ArrayList<>();
                                        JSONObject geojson = new JSONObject();
                                        doubles.add(longitude);
                                        doubles.add(latitude);
                                        geojson.put("type", "Point");
                                        geojson.put("coordinates", doubles);

                                        JSONObject kakouJsonObject = new JSONObject();
                                        kakouJsonObject.put("properties", json);
                                        kakouJsonObject.put("geometry", geojson);
                                        kakouList.add(kakouJsonObject);
                                    }
                                }
                            }

                        }
                    }
                }
                if (kakouList.size() > 0) {
                    roadJson.put("roadName", next);
                    roadJson.put("kakous", kakouList);
                    roadList.add(roadJson);
                }
            }
        }
        return roadList.toString();
    }

    @Override
    public String getKakouTrafficWarning(String dateType, String flowStartTime, String flowEndTime, String flowRate, String kkbh) {
        Calendar calendar1 = Calendar.getInstance();
        Calendar calendar = Calendar.getInstance();

        Set<String> monthSet = new HashSet<>();
        TreeSet<String> timeSet = new TreeSet<>();

        StringBuffer timeBuffer = new StringBuffer();
        StringBuffer realTimeTrafficbuffer = new StringBuffer();
        StringBuffer historicalAveragebuffer = new StringBuffer();
        StringBuffer apexbuffer = new StringBuffer();

        JSONObject jsonObject = new JSONObject();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        try {
            calendar1.setTime(sdf.parse(flowStartTime));
            calendar1.add(Calendar.MONTH, 1);
            String month = sdf.format(calendar1.getTime());

            monthSet.add(flowStartTime);
            monthSet.add(month);
            monthSet.add(flowEndTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        if (flowRate.equals("60")) {
            String hourTime = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());
            if (Integer.parseInt(hourTime.split(":")[1]) == 0) {
                calendar.add(Calendar.HOUR, -15);
            } else {
                calendar.add(Calendar.HOUR, -14);
            }

            for (int i = 0; i < 13; i++) {
                calendar.add(Calendar.HOUR, 1);
                String hourTimes = new SimpleDateFormat("yyyy/MM/dd HH:00:00").format(calendar.getTime());
                timeSet.add(hourTimes);
            }
            //实时流量
            JSONObject nowKakouLLJson = getKakoullByTimeSetKkbh(timeSet, kkbh, flowRate);

            Iterator<String> iterator1 = timeSet.iterator();
            while (iterator1.hasNext()) {
                String next = iterator1.next();
                if (nowKakouLLJson.containsKey(next)) {
                    int realKakouLL = (int) nowKakouLLJson.get(next);
                    realTimeTrafficbuffer.append(realKakouLL + ",");
                } else {
                    realTimeTrafficbuffer.append("0,");
                }
            }
            for (int i = 0; i < 12; i++) {
                calendar.add(Calendar.HOUR, 1);
                String hourTimes = new SimpleDateFormat("yyyy/MM/dd HH:00:00").format(calendar.getTime());
                timeSet.add(hourTimes);
            }
            //历史平均值和历史峰值
            JSONObject hisAvgMaxKakouLL = getAvgMaxKakouLlByTimeKkbh(timeSet, monthSet, dateType, flowRate, kkbh);
            Iterator<String> iterator2 = timeSet.iterator();
            while (iterator2.hasNext()) {
                String next = iterator2.next();
                if (hisAvgMaxKakouLL!=null&&hisAvgMaxKakouLL.containsKey(next.split(" ")[1])) {
                    String avgMaxKakooull = (String) hisAvgMaxKakouLL.get(next.split(" ")[1]);
                    String[] split = avgMaxKakooull.split(",");
                    historicalAveragebuffer.append(split[0]+",");
                    apexbuffer.append(split[1]+",");
                }else {
                    historicalAveragebuffer.append("0,");
                    apexbuffer.append("0,");
                }
            }
            Iterator<String> iterator = timeSet.iterator();
            while (iterator.hasNext()) {
                String next = iterator.next();
                String split = next.split(" ")[1];
                timeBuffer.append(split.split(":")[0] + split.split(":")[1] + ",");
            }

            String apexs = apexbuffer.toString();
            String historicalAverages = historicalAveragebuffer.toString();
            String realTimeTraffics = realTimeTrafficbuffer.toString();

            String[] apexsplit = apexs.split(",");
            String[] historicalAveragessplit = historicalAverages.split(",");
            String[] realTimeTrafficssplit = realTimeTraffics.split(",");

            int maxKakoull = Integer.parseInt(apexsplit[12]);
            int avgKakoull = Integer.parseInt(historicalAveragessplit[12]);
            int kakoull = Integer.parseInt(realTimeTrafficssplit[12]);

            int hisavgKakoull = Integer.parseInt(historicalAveragessplit[11]);
            int hiskakoull = Integer.parseInt(realTimeTrafficssplit[11]);

            int hiskakoull2 = Integer.parseInt(realTimeTrafficssplit[10]);

            if (kakoull>maxKakoull && kakoull>hiskakoull && hiskakoull>hiskakoull2){
                jsonObject.put("warminglevel","1");
            }else if (kakoull>avgKakoull && kakoull>hiskakoull && hiskakoull>hiskakoull2 && hiskakoull>hisavgKakoull){
                jsonObject.put("warminglevel","0");
            }
            jsonObject.put("realTimeTraffic", realTimeTrafficbuffer.toString());
            jsonObject.put("historicalAverage", historicalAveragebuffer.toString());
            jsonObject.put("apex", apexbuffer.toString());
            jsonObject.put("times", timeBuffer.toString());

        } else if (flowRate.equals("5")) {

            KakouTraffic kakouTraffic = new KakouTraffic();
            String hourTime = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());
            if (Integer.parseInt(hourTime.split(":")[1]) % 5 == 0) {
                calendar.add(Calendar.MINUTE, -70);
            } else {
                calendar.add(Calendar.MINUTE, -65);
            }

            for (int i = 0; i < 13; i++) {
                calendar.add(Calendar.MINUTE, 5);
                String hourTimes = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());
                String minute = kakouTraffic.getMinute(hourTimes);
                timeSet.add(minute);
            }
            //实时流量
            JSONObject nowKakouLLJson = getKakoullByTimeSetKkbh(timeSet, kkbh, flowRate);
            Iterator<String> iterator1 = timeSet.iterator();
            while (iterator1.hasNext()) {
                String next = iterator1.next();
                if (nowKakouLLJson.containsKey(next)) {
                    int realKakouLL = (int) nowKakouLLJson.get(next);
                    realTimeTrafficbuffer.append(realKakouLL + ",");
                } else {
                    realTimeTrafficbuffer.append("0,");
                }
            }

            for (int i = 0; i < 12; i++) {
                calendar.add(Calendar.MINUTE, 5);
                String hourTimes = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());
                String minute = kakouTraffic.getMinute(hourTimes);
                timeSet.add(minute);
            }

            JSONObject hisAvgMaxKakouLL = getAvgMaxKakouLlByTimeKkbh(timeSet, monthSet, dateType, flowRate, kkbh);
            Iterator<String> iterator2 = timeSet.iterator();
            while (iterator2.hasNext()) {
                String next = iterator2.next();
                if (hisAvgMaxKakouLL.containsKey(next.split(" ")[1])) {
                    String avgMaxKakooull = (String) hisAvgMaxKakouLL.get(next.split(" ")[1]);
                    String[] split = avgMaxKakooull.split(",");
                    historicalAveragebuffer.append(split[0]+",");
                    apexbuffer.append(split[1]+",");
                }else {
                    historicalAveragebuffer.append("0,");
                    apexbuffer.append("0,");
                }
            }

            Iterator<String> iterator = timeSet.iterator();
            while (iterator.hasNext()) {
                String next = iterator.next();
                String split = next.split(" ")[1];
                timeBuffer.append(split.split(":")[0] + split.split(":")[1] + ",");
            }
            String apexs = apexbuffer.toString();
            String historicalAverages = historicalAveragebuffer.toString();
            String realTimeTraffics = realTimeTrafficbuffer.toString();

            String[] apexsplit = apexs.split(",");
            String[] historicalAveragessplit = historicalAverages.split(",");
            String[] realTimeTrafficssplit = realTimeTraffics.split(",");

            int maxKakoull = Integer.parseInt(apexsplit[12]);
            int avgKakoull = Integer.parseInt(historicalAveragessplit[12]);
            int kakoull = Integer.parseInt(realTimeTrafficssplit[12]);

            int hisavgKakoull = Integer.parseInt(historicalAveragessplit[11]);
            int hiskakoull = Integer.parseInt(realTimeTrafficssplit[11]);

            int hiskakoull2 = Integer.parseInt(realTimeTrafficssplit[10]);

            if (kakoull>maxKakoull && kakoull>hiskakoull && hiskakoull>hiskakoull2){
                jsonObject.put("warminglevel","1");
            }else if (kakoull>avgKakoull && kakoull>hiskakoull && hiskakoull>hiskakoull2 && hiskakoull>hisavgKakoull){
                jsonObject.put("warminglevel","0");
            }
            jsonObject.put("realTimeTraffic", realTimeTrafficbuffer.toString());
            jsonObject.put("historicalAverage", historicalAveragebuffer.toString());
            jsonObject.put("apex", apexbuffer.toString());
            jsonObject.put("times", timeBuffer.toString());

        } else if (flowRate.equals("10")) {
            KakouTraffic kakouTraffic = new KakouTraffic();
            TreeSet<String> strings = new TreeSet<>();

            StringBuffer realTimeTraffic = new StringBuffer();
            StringBuffer historicalAverage = new StringBuffer();
            StringBuffer apex = new StringBuffer();

            String hourTime = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());

            if (Integer.parseInt(hourTime.split(":")[1]) == 0) {
                calendar.add(Calendar.MINUTE, -75);
            } else {
                calendar.add(Calendar.MINUTE, -70);
            }

            for (int i = 0; i < 14; i++) {
                calendar.add(Calendar.MINUTE, 5);
                String hourTimes = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());
                String minute = kakouTraffic.getMinute(hourTimes);
                timeSet.add(minute);
                if (i % 2 == 0) {
                    strings.add(minute);
                }
            }

            //实时流量
            JSONObject nowKakouLLJson = getKakoullByTimeSetKkbh(timeSet, kkbh, flowRate);

            Iterator<String> iterator1 = timeSet.iterator();
            while (iterator1.hasNext()) {
                String next = iterator1.next();
                if (nowKakouLLJson.containsKey(next)) {
                    int realKakouLL = (int) nowKakouLLJson.get(next);
                    realTimeTraffic.append(realKakouLL + ",");
                } else {
                    realTimeTraffic.append("0,");
                }
            }
            String[] split = realTimeTraffic.toString().split(",");
            for (int i = 0; i < split.length; i++) {
                if (i % 2 == 0) {
                    int realkkll = Integer.parseInt(split[i]) + Integer.parseInt(split[i + 1]);
                    realTimeTrafficbuffer.append(realkkll + ",");
                }
            }

            for (int i = 0; i < 12; i++) {
                calendar.add(Calendar.MINUTE, 5);
                String hourTimes = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());
                String minute = kakouTraffic.getMinute(hourTimes);
                timeSet.add(minute);
                if (i % 2 == 0) {
                    strings.add(minute);
                }
            }

            //历史平均值和历史峰值
            JSONObject hisAvgMaxKakouLL = getAvgMaxKakouLlByTimeKkbh(timeSet, monthSet, dateType, flowRate, kkbh);
            Iterator<String> iterator2 = timeSet.iterator();
            while (iterator2.hasNext()) {
                String next = iterator2.next();
                if (hisAvgMaxKakouLL.containsKey(next.split(" ")[1])) {
                    String avgMaxKakooull = (String) hisAvgMaxKakouLL.get(next.split(" ")[1]);
                    String[] split2 = avgMaxKakooull.split(",");
                    historicalAverage.append(split2[0]+",");
                    apex.append(split2[1]+",");
                }else {
                    historicalAverage.append("0,");
                    apex.append("0,");
                }
            }
            String[] split1 = historicalAverage.toString().split(",");
            String[] split4 = apex.toString().split(",");
            for (int i = 0; i < split1.length-1; i++) {
                if (i % 2 == 0) {
                    int reaAvglkkll = Integer.parseInt(split1[i])+Integer.parseInt(split1[i + 1]) ;
                    historicalAveragebuffer.append(reaAvglkkll + ",");
                    int reaMaxlkkll = Integer.parseInt(split4[i])+Integer.parseInt(split4[i + 1]);
                    apexbuffer.append(reaMaxlkkll + ",");
                }
            }
            Iterator<String> iterator = strings.iterator();
            while (iterator.hasNext()) {
                String next = iterator.next();
                String split3 = next.split(" ")[1];
                timeBuffer.append(split3.split(":")[0] + split3.split(":")[1] + ",");
            }

            String apexs = apexbuffer.toString();
            String historicalAverages = historicalAveragebuffer.toString();
            String realTimeTraffics = realTimeTrafficbuffer.toString();

            String[] apexsplit = apexs.split(",");
            String[] historicalAveragessplit = historicalAverages.split(",");
            String[] realTimeTrafficssplit = realTimeTraffics.split(",");

            int maxKakoull = Integer.parseInt(apexsplit[6]);
            int avgKakoull = Integer.parseInt(historicalAveragessplit[6]);
            int kakoull = Integer.parseInt(realTimeTrafficssplit[6]);

            int hisavgKakoull = Integer.parseInt(historicalAveragessplit[5]);
            int hiskakoull = Integer.parseInt(realTimeTrafficssplit[5]);

            int hiskakoull2 = Integer.parseInt(realTimeTrafficssplit[4]);

            if (kakoull>maxKakoull && kakoull>hiskakoull && hiskakoull>hiskakoull2){
                jsonObject.put("warminglevel","1");
            }else if (kakoull>avgKakoull && kakoull>hiskakoull && hiskakoull>hiskakoull2 && hiskakoull>hisavgKakoull){
                jsonObject.put("warminglevel","0");
            }

            jsonObject.put("realTimeTraffic", realTimeTrafficbuffer.toString());
            jsonObject.put("historicalAverage", historicalAveragebuffer.toString());
            jsonObject.put("apex", apexbuffer.toString());
            jsonObject.put("times", timeBuffer.toString());
        }

        return jsonObject.toString();
    }

//    @Override
//    public String getRoadApplication(final String roadname) {
//        Connection connection = null;
//        PreparedStatement statement = null;
//        ResultSet resultSet = null;
//        JSONObject roadJSONObject = new JSONObject();
//        try {
//            Set<String> roadids = new HashSet<>();
//            String  sql;
//            if (roadname.equals("all")){
//                sql = "SELECT rp.roaddirection,rp.roadname,rp.roadid,s.roadstartendlont " +
//                        "from ROAD_PIECEWISE_DATA s ,swan_road_piecewise_middle_new sr,ROAD_PIECEWISE_DATA_NEW rp " +
//                        "WHERE sr.id=s.id and sr.roadid=rp.roadid ";
//            }else {
//                sql = "SELECT rp.roaddirection,rp.roadname,rp.roadid,s.roadstartendlont " +
//                        "from ROAD_PIECEWISE_DATA s ,swan_road_piecewise_middle_new sr,ROAD_PIECEWISE_DATA_NEW rp " +
//                        "WHERE sr.id=s.id and sr.roadid=rp.roadid AND rp.roadname like ?";
//            }
//            connection =jdbcTemplate.getDataSource().getConnection();
//            statement = connection.prepareStatement(sql);
//            if (!roadname.equals("all")){
//                statement.setString(1,"%"+roadname+"%");
//            }
//            resultSet = statement.executeQuery();
//            JSONObject jsonObject = new JSONObject();
//            while (resultSet.next()){
//                String roadid = resultSet.getString("roadid");
//                String roadapplication = resultSet.getString("roadname");
//                String roaddirection = resultSet.getString("roaddirection");
//                String  roadstartendlont = resultSet.getString("roadstartendlont");
//                if (jsonObject.containsKey(roadid)){
//                    String roadstartendlonts = (String) jsonObject.get(roadid);
//                    roadstartendlonts = roadstartendlonts + " "+ roadstartendlont;
//                    jsonObject.put(roadid,roadstartendlonts);
//                }else {
//                    jsonObject.put(roadid,roadstartendlont);
//                }
//                jsonObject.put(roadid+"name",roadapplication);
//                jsonObject.put(roadid+"direction",roaddirection);
//                roadids.add(roadid);
//            }
//            ArrayList<JSONObject> jsonObjects = new ArrayList<>();
//
//            for (String roadid : roadids) {
//                JSONObject json = new JSONObject();
//                List<List<Double>> lists = new ArrayList<>();
//                JSONObject roadJsonObject = new JSONObject();
//                JSONObject geojson = new JSONObject();
//                if (jsonObject.containsKey(roadid)){
//                    String roadstartendlonts = (String) jsonObject.get(roadid);
//                    String roadapplication = (String) jsonObject.get(roadid+"name");
//                    String roaddirection = (String) jsonObject.get(roadid+"direction");
//                    String[] splits = roadstartendlonts.split(" ");
//                    JSONObject lontJson = new JSONObject();
//                    List<String> longitudes = new ArrayList<>();
//                    List<String> latitudes = new ArrayList<>();
//                    for (int i = 0; i < splits.length; i++) {
//                        String[] split = splits[i].split(";");
//                        lontJson.put(split[0]+"经度",split[1]);
//                        longitudes.add(split[0]);
//
//                        lontJson.put(split[1]+"纬度",split[0]);
//                        latitudes.add(split[1]);
//                    }
//                    List<String> lonts = null;
//                    if (roaddirection.equals("S-N")){
//                        Collections.sort(latitudes, new Comparator<String>() {
//                            @Override
//                            public int compare(String o1, String o2) {
//                                return o1.compareTo(o2);
//                            }
//                        });
//                        lonts = latitudes;
//                    }else if (roaddirection.equals("N-S")){
//                        Collections.sort(latitudes, new Comparator<String>() {
//                            @Override
//                            public int compare(String o1, String o2) {
//                                return o2.compareTo(o1);
//                            }
//                        });
//                        lonts = latitudes;
//                    }else if (roaddirection.equals("E-W")){
//                        Collections.sort(longitudes, new Comparator<String>() {
//                            @Override
//                            public int compare(String o1, String o2) {
//                                return o2.compareTo(o1);
//                            }
//                        });
//                        lonts = longitudes;
//                    }else if (roaddirection.equals("W-E")){
//                        Collections.sort(longitudes, new Comparator<String>() {
//                            @Override
//                            public int compare(String o1, String o2) {
//                                return o1.compareTo(o2);
//                            }
//                        });
//                        lonts = longitudes;
//                    }
//                    for (int i = 0; i < lonts.size(); i++) {
//                        if (lonts==latitudes){
//                            double longitude1 = lontJson.getDouble(lonts.get(i) + "纬度");//经度
//                            List<Double> doubles = new ArrayList<>();
//                            doubles.add(longitude1);
//                            doubles.add(Double.parseDouble(lonts.get(i)));
//                            lists.add(doubles);
//                        }else {
//                            double latitude1 = lontJson.getDouble(lonts.get(i) + "经度");//纬度
//                            List<Double> doubles = new ArrayList<>();
//                            doubles.add(Double.parseDouble(lonts.get(i)));
//                            doubles.add(latitude1);
//                            lists.add(doubles);
//                        }
//                    }geojson.put("coordinates", lists);
//                    json.put("roadName",roadapplication);
//                }
//                json.put("roadId",roadid);
//
//
//                geojson.put("type", "LineString");
//
//                roadJsonObject.put("properties", json);
//                roadJsonObject.put("geometry", geojson);
//                jsonObjects.add(roadJsonObject);
//            }
//            Collections.sort(jsonObjects, new Comparator<JSONObject>() {
//                @Override
//                public int compare(JSONObject o1, JSONObject o2) {
//
//                    JSONObject properties1 = o1.getJSONObject("properties");
//                    JSONObject properties2 = o2.getJSONObject("properties");
//                    String roadName1 = properties1.getString("roadName").split("-")[0];
//                    String roadName2 = properties2.getString("roadName").split("-")[0];
//                    if (roadName1.contains(roadname)&&roadName2.contains(roadname)){
//                        return -1;
//                    }else if (!roadName1.contains(roadname)&&roadName2.contains(roadname)){
//                        return 1;
//                    }else if (roadName1.contains(roadname)&&!roadName2.contains(roadname)){
//                        return -1;
//                    }else {
//                        return roadName2.compareTo(roadName1);
//                    }
//                }
//            });
//            roadJSONObject.put("roads",jsonObjects);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }finally {
//            try {
//                if (resultSet != null) {resultSet.close();}
//                if (statement != null) {statement.close();}
//                if (connection != null) {connection.close();}
//            } catch (Exception e) {
//                e.getStackTrace();
//            }
//        }
//        return roadJSONObject.toString();
//    }
    @Override
    public String getRoadApplication(final String roadname) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        JSONObject roadJSONObject = new JSONObject();
        try {
            Set<String> roadids = new HashSet<>();
            String  sql;
            if (roadname.equals("all")){
                sql = "SELECT rp.roaddirection,rp.roadname,rp.roadid,rp.roadlength,rp.kind,s.roadstartendlont " +
                        "from ROAD_PIECEWISE_DATA s ,swan_road_piecewise_middle sr,ROAD_PIECEWISE_DATA_NEW_T rp " +
                        "WHERE sr.id=s.id and sr.roadid=rp.roadid ";
            }else {
                sql = "SELECT rp.roaddirection,rp.roadname,rp.roadid,rp.roadlength,rp.kind,s.roadstartendlont " +
                        "from ROAD_PIECEWISE_DATA s ,swan_road_piecewise_middle sr,ROAD_PIECEWISE_DATA_NEW_T rp " +
                        "WHERE sr.id=s.id and sr.roadid=rp.roadid AND rp.roadname like ?";
            }
            connection =jdbcTemplate.getDataSource().getConnection();
            statement = connection.prepareStatement(sql);
            if (!roadname.equals("all")){
                statement.setString(1,"%"+roadname+"%");
            }
            resultSet = statement.executeQuery();
            JSONObject jsonObject = new JSONObject();
            while (resultSet.next()){
                String roadid = resultSet.getString("roadid");
                String roadapplication = resultSet.getString("roadname");
                String roaddirection = resultSet.getString("roaddirection");
                String  roadstartendlont = resultSet.getString("roadstartendlont");
                String  roadlength = resultSet.getString("roadlength");
                String  kind = resultSet.getString("kind");
                if (jsonObject.containsKey(roadid)){
                    String roadstartendlonts = (String) jsonObject.get(roadid);
                    roadstartendlonts = roadstartendlonts + " "+ roadstartendlont;
                    jsonObject.put(roadid,roadstartendlonts);
                }else {
                    jsonObject.put(roadid,roadstartendlont);
                }
                jsonObject.put(roadid+"name",roadapplication);
                jsonObject.put(roadid+"direction",roaddirection);
                jsonObject.put(roadid+"roadlength",roadlength);
                jsonObject.put(roadid+"kind",kind);
                roadids.add(roadid);
            }
            ArrayList<JSONObject> jsonObjects = new ArrayList<>();

            for (String roadid : roadids) {
                JSONObject json = new JSONObject();
                List<List<Double>> lists = new ArrayList<>();
                JSONObject roadJsonObject = new JSONObject();
                JSONObject geojson = new JSONObject();
                if (jsonObject.containsKey(roadid)){
                    String roadstartendlonts = (String) jsonObject.get(roadid);
                    String roadapplication = (String) jsonObject.get(roadid+"name");
                    String roadlength = jsonObject.getString(roadid+"roadlength");
                    String kind = jsonObject.getString(roadid+"kind");
                    String[] splits = roadstartendlonts.split(" ");
                        JSONObject lontJson = new JSONObject();
                        for (int i = 0; i < splits.length; i++) {
                            String[] split = splits[i].split(";");
                            lontJson.put(split[0]+"经度",split[1]);
                            List<Double> doubles = new ArrayList<>();
                            doubles.add(Double.parseDouble(split[0]));
                            doubles.add(Double.parseDouble(split[1]));
                            lists.add(doubles);
                        }
                        geojson.put("coordinates", lists);
                        json.put("roadName",roadapplication);
                        json.put("roadlength",roadlength);
                        json.put("kind",kind);
                }
                json.put("roadId",roadid);
                geojson.put("type", "LineString");

                roadJsonObject.put("properties", json);
                roadJsonObject.put("geometry", geojson);
                jsonObjects.add(roadJsonObject);
            }
            Collections.sort(jsonObjects, new Comparator<JSONObject>() {
                @Override
                public int compare(JSONObject o1, JSONObject o2) {

                    JSONObject properties1 = o1.getJSONObject("properties");
                    JSONObject properties2 = o2.getJSONObject("properties");
                    String roadName1 = properties1.getString("roadName").split("-")[0];
                    String roadName2 = properties2.getString("roadName").split("-")[0];
                    if (roadName1.contains(roadname)&&roadName2.contains(roadname)){
                        return -1;
                    }else if (!roadName1.contains(roadname)&&roadName2.contains(roadname)){
                        return 1;
                    }else if (roadName1.contains(roadname)&&!roadName2.contains(roadname)){
                        return -1;
                    }else {
                        return roadName2.compareTo(roadName1);
                    }
                }
            });
            roadJSONObject.put("roads",jsonObjects);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (resultSet != null) {resultSet.close();}
                if (statement != null) {statement.close();}
                if (connection != null) {connection.close();}
            } catch (Exception e) {
                e.getStackTrace();
            }
        }
        return roadJSONObject.toString();
    }

//    public String getRoadApplication(String roadname) {
//        Connection connection = null;
//        PreparedStatement statement = null;
//        ResultSet resultSet = null;
//        PreparedStatement statement2 = null;
//        ResultSet resultSet2 = null;
//        JSONObject roadJSONObject = new JSONObject();
//        try {
//            String  sql1;
//            if (roadname.equals("all")){
//                sql1 = "SELECT roadapplication ,roadid from swan_road_s ";
//            }else {
//                sql1 = "SELECT roadapplication ,roadid from swan_road_s  WHERE roadname LIKE ?";
//            }
//
//            connection = jdbcTemplate.getDataSource().getConnection();
//            statement = connection.prepareStatement(sql1);
//            if (!roadname.equals("all")){
//                statement.setString(1,"%"+roadname+"%");
//            }
//            resultSet = statement.executeQuery();
//            JSONObject propJsonObject = new JSONObject();
//            List<String> roadids = new ArrayList<>();
//            while (resultSet.next()){
//                String roadapplication = resultSet.getString("roadapplication");
//                String roadid = resultSet.getString("roadid");
//                propJsonObject.put(roadid,roadapplication);
//                roadids.add(roadid);
//            }
//            String  sql2 = "SELECT s.roadapplication,s.roadid,rp.roadstartendlont " +
//                    "from swan_road_s s ,swan_road_piecewise_middle sr,ROAD_PIECEWISE_DATA rp " +
//                    "WHERE sr.roadidlw=s.roadid and sr.roadidgt=rp.id " +
//                    "and s.roadid IN ("+org.apache.commons.lang.StringUtils.repeat("?", ",", roadids.size())+")";
//            statement2 = connection.prepareStatement(sql2);
//            for (int i = 1; i < roadids.size()+1; i++) {
//                statement2.setString(i,roadids.get(i-1));
//            }
//            resultSet2 = statement2.executeQuery();
//            JSONObject jsonObject = new JSONObject();
//            while (resultSet2.next()){
//                String roadid = resultSet2.getString("roadid");
//                String  roadstartendlont = resultSet2.getString("roadstartendlont");
//                if (jsonObject.containsKey(roadid)){
//                    String roadstartendlonts = (String) jsonObject.get(roadid);
//                    roadstartendlonts = roadstartendlonts + " "+ roadstartendlont;
//                    jsonObject.put(roadid,roadstartendlonts);
//                }else {
//                    jsonObject.put(roadid,roadstartendlont);
//                }
//            }
//            ArrayList<JSONObject> jsonObjects = new ArrayList<>();
//
//            for (String roadid : roadids) {
//                String roadapplication = (String) propJsonObject.get(roadid);
//
//                JSONObject json = new JSONObject();
//                List<List<Double>> lists = new ArrayList<>();
//                JSONObject roadJsonObject = new JSONObject();
//                JSONObject geojson = new JSONObject();
//
//                if (jsonObject.containsKey(roadid)){
//                    String roadstartendlonts = (String) jsonObject.get(roadid);
//                    String[] splits = roadstartendlonts.split(" ");
//                    JSONObject lontJson = new JSONObject();
//                    List<String> longitudes = new ArrayList<>();
//                    List<String> latitudes = new ArrayList<>();
//                    for (int i = 0; i < splits.length; i++) {
//                        String[] split = splits[i].split(";");
//                        lontJson.put(split[0]+"经度",split[1]);
//                        longitudes.add(split[0]);
//
//                        lontJson.put(split[1]+"纬度",split[0]);
//                        latitudes.add(split[1]);
//                    }
//                    String[] lontsSplit = roadapplication.split("-");
//                    List<String> lonts = null;
//                    if (lontsSplit[2].equals("南向北")){
//                        Collections.sort(latitudes, new Comparator<String>() {
//                            @Override
//                            public int compare(String o1, String o2) {
//                                return o1.compareTo(o2);
//                            }
//                        });
//                        lonts = latitudes;
//                    }else if (lontsSplit[2].equals("北向南")){
//                        Collections.sort(latitudes, new Comparator<String>() {
//                            @Override
//                            public int compare(String o1, String o2) {
//                                return o2.compareTo(o1);
//                            }
//                        });
//                        lonts = latitudes;
//                    }else if (lontsSplit[2].equals("东向西")){
//                        Collections.sort(longitudes, new Comparator<String>() {
//                            @Override
//                            public int compare(String o1, String o2) {
//                                return o2.compareTo(o1);
//                            }
//                        });
//                        lonts = longitudes;
//                    }else if (lontsSplit[2].equals("西向东")){
//                        Collections.sort(longitudes, new Comparator<String>() {
//                            @Override
//                            public int compare(String o1, String o2) {
//                                return o1.compareTo(o2);
//                            }
//                        });
//                        lonts = longitudes;
//                    }
//                    for (int i = 0; i < lonts.size(); i++) {
//                        if (lonts==latitudes){
//                            double longitude1 = lontJson.getDouble(lonts.get(i) + "纬度");//经度
//                            List<Double> doubles = new ArrayList<>();
//                            doubles.add(longitude1);
//                            doubles.add(Double.parseDouble(lonts.get(i)));
//                            lists.add(doubles);
//                        }else {
//                            double latitude1 = lontJson.getDouble(lonts.get(i) + "经度");//纬度
//                            List<Double> doubles = new ArrayList<>();
//                            doubles.add(Double.parseDouble(lonts.get(i)));
//                            doubles.add(latitude1);
//                            lists.add(doubles);
//                        }
//                    }geojson.put("coordinates", lists);
//                }
//                json.put("roadId",roadid);
//                json.put("roadName",roadapplication);
//
//                geojson.put("type", "LineString");
//
//                roadJsonObject.put("properties", json);
//                roadJsonObject.put("geometry", geojson);
//                jsonObjects.add(roadJsonObject);
//            }
//            roadJSONObject.put("roads",jsonObjects);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }finally {
//            try {
//                if (resultSet != null) {resultSet.close();}
//                if (statement != null) {statement.close();}
//                if (resultSet2 != null) {resultSet2.close();}
//                if (statement2 != null) {statement2.close();}
//                if (connection != null) {connection.close();}
//            } catch (Exception e) {
//                e.getStackTrace();
//            }
//        }
//        return roadJSONObject.toString();
//    }

    @Override
    public String getRoadSignalData(String roadId, String startTime1, String startTime2, String endTime1, String endTime2) {

        JSONObject roadIndexJSON = new JSONObject();

        //获取改路段的最大最小旅行时间
        JSONObject object = getMinMaxTravelTime(roadId);

//        String start1 = startTime1.split(" ")[0];
//        String end1 = endTime1.split(" ")[0];
//        String start2 = startTime2.split(" ")[0];
//        String end2 = endTime2.split(" ")[0];
//        if (start1.equals(end1)){
            //获取开始结束时间间的信号数据
            JSONObject indexInfo1 = getIndexInfo(roadId, startTime1, endTime1);
            if (indexInfo1!=null&&!indexInfo1.isEmpty()){
                indexInfo1.put("maxTravelTime",object.getString("maxTravelTime"));
                indexInfo1.put("minTravelTime",object.getString("minTravelTime"));
                roadIndexJSON.put("result1",indexInfo1);
            }else {
                roadIndexJSON.put("result1",object);
            }
//        }else {
//            roadIndexJSON.put("result1","传入时间不符合要求");
//        }
//        if (start2.equals(end2)){
            //获取开始结束时间间的信号数据
            JSONObject indexInfo2 = getIndexInfo(roadId, startTime2, endTime2);
            if (indexInfo2!=null&&!indexInfo2.isEmpty()){
                indexInfo2.put("maxTravelTime",object.getString("maxTravelTime"));
                indexInfo2.put("minTravelTime",object.getString("minTravelTime"));
                roadIndexJSON.put("result2",indexInfo2);
            }else {
                roadIndexJSON.put("result2",object);
            }
//        }else {
//            roadIndexJSON.put("result2","传入时间不符合要求");
//        }
        String jsonString = JSON.toJSONString(roadIndexJSON, SerializerFeature.DisableCircularReferenceDetect);
        return jsonString;
    }

    public JSONObject getMinMaxTravelTime(String roadId){
        SqlRowSet rs = null;
        String sql = "SELECT MAXTRAVELTIME,MINTRAVELTIME  FROM road_piecewise_data_new_t   WHERE roadid=? ";
        rs = jdbcTemplate.queryForRowSet(sql,roadId);
        JSONObject jsonObject = new JSONObject();
        while (rs.next()){
            jsonObject.put("maxTravelTime",rs.getString(1));
            jsonObject.put("minTravelTime",rs.getString(2));
        }
        return jsonObject;
    }

    public JSONObject getIndexInfo(String roadId,String startTime,String endTime){

        SqlRowSet rs = null;
        String sql = "SELECT round(AVG(AVGSPEED) ::NUMERIC, 2) AVGSPEED," +
                    "round(AVG(TRAVELTIME) ::NUMERIC, 2) TRAVELTIME," +
                    "round(AVG(ROADINDEX) ::NUMERIC, 2) ROADINDEX " +
                    "from  road_index_traveltime_info_2018  WHERE TIME >= ? AND TIME<= ? AND roadid=?  GROUP BY roadid";

        JSONObject roadJsonStart = new JSONObject();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddHHmm");
        try {
            long starttime = Long.valueOf(sdf2.format(sdf.parse(startTime)));
            long endtime = Long.valueOf(sdf2.format(sdf.parse(endTime)));
            rs = jdbcTemplatePG.queryForRowSet(sql,starttime,endtime,roadId);
            while (rs.next()){
                double avgspeed = rs.getDouble(1);
                double traveltime = rs.getDouble(2);
                double roadindex = rs.getDouble(3);
                roadJsonStart.put("averageSpeed",avgspeed);
                roadJsonStart.put("averageTravelTime",traveltime);
                roadJsonStart.put("trafficIndex",roadindex);
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return roadJsonStart;
    }

    @Override
    public String getGSXRoadInformation(String roadType) {
        Connection connection =null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        List<JSONObject> jsonObjects = new ArrayList<>();
        try {
            connection = jdbcTemplate.getDataSource().getConnection();
            System.out.println("connection :" + connection.toString());
            String format = new SimpleDateFormat("yyyyMM").format(new Date());
            Calendar calendar = Calendar.getInstance();
            String format1 = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());
            if (format1.split(":")[1].equals("00")){
                calendar.add(Calendar.HOUR,-2);
            }else {
                calendar.add(Calendar.HOUR,-1);
            }
            String format2 = new SimpleDateFormat("yyyy/MM/dd HH:00:00").format(calendar.getTime());
            System.out.println("format2:"+format2);
                String  sql = "select c.xjmc,c.xjbh,d.LONGITUDE,d.LATITUDE,c.xjll,c.tjsd from CTL_XJLL_HOUR_HISTORY_" +format+" c,CTL_DM_GSX_INFO d where c.xjbh=d.dmid and c.tjsd=?";
                statement = connection.prepareStatement(sql);
                statement.setString(1,format2);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                String dmmc = resultSet.getString("xjmc");
//                System.out.println("dmmc:"+dmmc);
                if (!roadType.equals("A")){
                    String[] split = dmmc.split("-");
                    if (roadType.equals("G")){
                        if (split[0].contains("G")||split[0].contains("国道")){
                            String dmid = resultSet.getString("xjbh");
                            String tjsd = resultSet.getString("tjsd");
                            double gisX = resultSet.getDouble("LONGITUDE");
                            double gisY = resultSet.getDouble("LATITUDE");
                            int xjll = resultSet.getInt("xjll");
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("dmmc",dmmc);
                            jsonObject.put("dmid",dmid);
                            jsonObject.put("tjsd",tjsd);
                            jsonObject.put("gisX",gisX);
                            jsonObject.put("gisY",gisY);
                            jsonObject.put("xjll",xjll);
                            jsonObjects.add(jsonObject);
                        }
                    }else if (roadType.equals("S")){
                        if (!split[0].contains("G") && (split[0].contains("S")||split[0].contains("省道"))){
                            String dmid = resultSet.getString("xjbh");
                            String tjsd = resultSet.getString("tjsd");
                            double gisX = resultSet.getDouble("LONGITUDE");
                            double gisY = resultSet.getDouble("LATITUDE");
                            int xjll = resultSet.getInt("xjll");
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("dmmc",dmmc);
                            jsonObject.put("dmid",dmid);
                            jsonObject.put("tjsd",tjsd);
                            jsonObject.put("gisX",gisX);
                            jsonObject.put("gisY",gisY);
                            jsonObject.put("xjll",xjll);
                            jsonObjects.add(jsonObject);
                        }
                    }else {
                        if (split[0].contains("X")||split[0].contains("县道")){
                            String dmid = resultSet.getString("xjbh");
                            String tjsd = resultSet.getString("tjsd");
                            double gisX = resultSet.getDouble("LONGITUDE");
                            double gisY = resultSet.getDouble("LATITUDE");
                            int xjll = resultSet.getInt("xjll");
                            JSONObject jsonObject = new JSONObject();
                            jsonObject.put("dmmc",dmmc);
                            jsonObject.put("dmid",dmid);
                            jsonObject.put("tjsd",tjsd);
                            jsonObject.put("gisX",gisX);
                            jsonObject.put("gisY",gisY);
                            jsonObject.put("xjll",xjll);
                            jsonObjects.add(jsonObject);
                        }
                    }
                }else {
                    String dmid = resultSet.getString("xjbh");
                    String tjsd = resultSet.getString("tjsd");
                    double gisX = resultSet.getDouble("LONGITUDE");
                    double gisY = resultSet.getDouble("LATITUDE");
                    int xjll = resultSet.getInt("xjll");
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("dmmc",dmmc);
                    jsonObject.put("dmid",dmid);
                    jsonObject.put("tjsd",tjsd);
                    jsonObject.put("gisX",gisX);
                    jsonObject.put("gisY",gisY);
                    jsonObject.put("xjll",xjll);
                    jsonObjects.add(jsonObject);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
//            System.out.println("jsonObjects结果："+jsonObjects.toString());
            closeOracle(resultSet,statement,connection);
        }
        return jsonObjects.toString();
    }

    @Override
    public String getDMFlowRateByDeviceId(String deviceId) {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        TreeSet<String> timeSet = new TreeSet<>();
        Calendar calendar = Calendar.getInstance();
        String hourTime = new SimpleDateFormat("yyyy/MM/dd HH:mm:00").format(calendar.getTime());
        String format = new SimpleDateFormat("yyyy/MM/dd").format(new Date());
        String categoryname = null;
        try {
            connection = jdbcTemplate.getDataSource().getConnection();
            String sql = "select CATEGORYNAME from N_WORKDAYS where tjsd=?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,format);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                categoryname = resultSet.getString("CATEGORYNAME");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeOracle(resultSet,statement,connection);
        }

        JSONObject jsonObject = new JSONObject();
        StringBuffer timeBuffer = new StringBuffer();
        StringBuffer realTimeTrafficbuffer = new StringBuffer();
        StringBuffer historicalAveragebuffer = new StringBuffer();
        StringBuffer apexbuffer = new StringBuffer();

        if (Integer.parseInt(hourTime.split(":")[1]) == 0) {
            calendar.add(Calendar.HOUR, -15);
        } else {
            calendar.add(Calendar.HOUR, -14);
        }

        for (int i = 0; i < 13; i++) {
            calendar.add(Calendar.HOUR, 1);
            String hourTimes = new SimpleDateFormat("yyyy/MM/dd HH:00:00").format(calendar.getTime());
            timeSet.add(hourTimes);
        }
        //实时流量
        JSONObject nowKakouLLJson = getKakoullByTimeSetKkbh(timeSet, deviceId, "60");

        Iterator<String> iterator1 = timeSet.iterator();
        while (iterator1.hasNext()) {
            String next = iterator1.next();
            if (nowKakouLLJson.containsKey(next)) {
                int realKakouLL = (int) nowKakouLLJson.get(next);
                realTimeTrafficbuffer.append(realKakouLL + ",");
            } else {
                realTimeTrafficbuffer.append("0,");
            }
        }
        for (int i = 0; i < 12; i++) {
            calendar.add(Calendar.HOUR, 1);
            String hourTimes = new SimpleDateFormat("yyyy/MM/dd HH:00:00").format(calendar.getTime());
            timeSet.add(hourTimes);
        }
        String dateType;
        if (categoryname==null){dateType = "work";}
        else if (categoryname.equals("nowork")){dateType = "nwork";}
        else {dateType = "holi";}

        //历史平均值和历史峰值
        JSONObject avgMaxJson = getAvgMaxFromMongo(timeSet, deviceId, dateType);
        Iterator<String> iterator2 = timeSet.iterator();
        while (iterator2.hasNext()) {
            String next = iterator2.next();
            if (avgMaxJson!=null&&avgMaxJson.containsKey(next.split(" ")[1])){
                JSONObject flowJson = avgMaxJson.getJSONObject(next.split(" ")[1]);
                int avgKkll = flowJson.getInteger("avgkkll");
                int maxKkll = flowJson.getInteger("maxkkll");
                historicalAveragebuffer.append(avgKkll+",");
                apexbuffer.append(maxKkll+",");
            }else {
                historicalAveragebuffer.append("0,");
                apexbuffer.append("0,");
            }
        }
        Iterator<String> iterator = timeSet.iterator();
        while (iterator.hasNext()) {
            String next = iterator.next();
            String split = next.split(" ")[1];
            timeBuffer.append(split.split(":")[0] + split.split(":")[1] + ",");
        }

        String apexs = apexbuffer.toString();
        String historicalAverages = historicalAveragebuffer.toString();
        String realTimeTraffics = realTimeTrafficbuffer.toString();

        String[] apexsplit = apexs.split(",");
        String[] historicalAveragessplit = historicalAverages.split(",");
        String[] realTimeTrafficssplit = realTimeTraffics.split(",");

        int maxKakoull = Integer.parseInt(apexsplit[12]);
        int avgKakoull = Integer.parseInt(historicalAveragessplit[12]);
        int kakoull = Integer.parseInt(realTimeTrafficssplit[12]);

        int hisavgKakoull = Integer.parseInt(historicalAveragessplit[11]);
        int hiskakoull = Integer.parseInt(realTimeTrafficssplit[11]);

        int hiskakoull2 = Integer.parseInt(realTimeTrafficssplit[10]);

        if (kakoull>maxKakoull && kakoull>hiskakoull && hiskakoull>hiskakoull2){
            jsonObject.put("warminglevel","1");
        }else if (kakoull>avgKakoull && kakoull>hiskakoull && hiskakoull>hiskakoull2 && hiskakoull>hisavgKakoull){
            jsonObject.put("warminglevel","0");
        }
        jsonObject.put("realTimeTraffic", realTimeTrafficbuffer.toString());
        jsonObject.put("historicalAverage", historicalAveragebuffer.toString());
        jsonObject.put("apex", apexbuffer.toString());
        jsonObject.put("times", timeBuffer.toString());

        return jsonObject.toString();
    }

    @Override
    public List<Map<String, Object>> getRoadIndexMeger(String starttime, String endtime, String month,String type) {

        String  sql;
        if (type.equals("1")){
            sql = "SELECT RS.\"ID\", ( b.ROADNAME || '(' || b.STARTNAME || '-->' || b.ENDNAME || ')' ) AS roadname, RS.AVGSPEED, RS.MAXSPEED, RS.MINSPEED, RS.AVGINDEX, RS.MAXINDEX, RS.MININDEX, ROUND ( (b.\"LENGTH\" / RS.AVGSPEED) * 3.6 ) AS avgtraveltime FROM ( SELECT A .\"ID\", ROUND (AVG(A .AVGSPEED), 2) AS avgspeed, MAX (A .AVGSPEED) AS maxspeed, MIN (A .AVGSPEED) AS minspeed, ROUND (AVG(A .C_INDEX), 2) AS avgindex, MAX (A .C_INDEX) AS maxindex, MIN (A .C_INDEX) AS minindex FROM INDEX_TPI_ROAD_150200_"+month+" A WHERE 1 = 1 AND CAST (A .\"TIME\" AS NUMBER) >= "+starttime+" AND CAST (A .\"TIME\" AS NUMBER) <= "+endtime+" GROUP BY A .\"ID\" ) rs, INDEX_ROAD_INFO b WHERE RS.\"ID\" = b.\"ID\"";
        }else {
            sql = "SELECT A .\"ID\", \"SUBSTR\" (A .\"TIME\", 9, 2) as HOUR, ROUND(AVG(A .AVGSPEED), 2) AS AVGSPEED, ROUND(AVG(A .C_INDEX), 2) AS AVGINDEX FROM INDEX_TPI_ROAD_150200_"+month+" A WHERE 1 = 1 AND CAST (A .\"TIME\" AS NUMBER) >="+starttime+"  AND CAST (A .\"TIME\" AS NUMBER) <= "+endtime+" GROUP BY A .\"ID\", \"SUBSTR\"(a.\"TIME\", 9, 2) ORDER BY HOUR ";
        }
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> getAreaIndexmeger(String starttime, String endtime, String month,String type) {
        String  sql;
        if (type.equals("1")){
            sql = "SELECT RS.AREA, b.TEAMNAME, RS.AVGSPEED, RS.MAXSPEED, RS.MINSPEED, RS.AVGINDEX, RS.MAXINDEX, RS.MININDEX, ROUND ( (b.\"LENGTH\" / RS.AVGSPEED) * 3.6 ) AS avgtraveltime FROM ( SELECT A .AREA, ROUND (AVG(A .AVGSPEED), 2) AS avgspeed, MAX (A .AVGSPEED) AS maxspeed, MIN (A .AVGSPEED) AS minspeed, ROUND (AVG(A .C_INDEX), 2) AS avgindex, MAX (A .C_INDEX) AS maxindex, MIN (A .C_INDEX) AS minindex FROM INDEX_TPI_150200_"+month+" A WHERE 1 = 1 AND CAST (A .\"TIME\" AS NUMBER) >= "+starttime+" AND CAST (A .\"TIME\" AS NUMBER) <= "+endtime+" GROUP BY A .AREA ) rs, ( SELECT A .TEAMID, A .TEAMNAME, A.LENGTH FROM INDEX_TEAM_LENGTH A ) b WHERE RS.AREA = b.TEAMID";
        }else {
            sql = "SELECT A .AREA, \"SUBSTR\"(a.\"TIME\", 9, 2) as HOUR, ROUND (AVG(A .AVGSPEED), 2) AS AVGSPEED, ROUND (AVG(A .C_INDEX), 2) AS AVGINDEX FROM INDEX_TPI_150200_"+month+" A WHERE 1 = 1 AND CAST (A .\"TIME\" AS NUMBER) >= "+starttime+" AND CAST (A .\"TIME\" AS NUMBER) <= "+endtime+" GROUP BY A .AREA,\"SUBSTR\"(a.\"TIME\", 9, 2) ORDER BY HOUR";
        }
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> getRoadPredictBaseFromOracleWork(String yearMonth) {
        String sql = "SELECT s.*,round((b.\"LENGTH\"/s.AVGSPEED1)*3.6) as traveltime FROM ( SELECT A .\"ID\", round(AVG (A .AVGSPEED),2) AS avgspeed1, round(AVG (A .C_INDEX),2) AS index1, \"SUBSTR\" (A .\"TIME\", 9, 4) AS time_1 FROM INDEX_TPI_ROAD_150200_"+yearMonth+" A WHERE cast(\"SUBSTR\" (A .\"TIME\", 1, 8) as NUMBER) not in (SELECT cast(replace(d.tjsd,'/','') as NUMBER) as tjsd1 from N_WORKDAYS d WHERE d.tjsd LIKE '"+yearMonth.substring(0,4)+"/"+yearMonth.substring(4)+"%' ) GROUP BY A .\"ID\", \"SUBSTR\" (A .\"TIME\", 9, 4) ORDER BY time_1 ) s, INDEX_ROAD_INFO b WHERE b. ID = s. ID";
        System.out.println(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> getRoadPredictBaseFromOracleNoWork(String yearMonth) {
        String sql = "SELECT s.*,round((b.\"LENGTH\"/s.AVGSPEED1)*3.6) as traveltime FROM ( SELECT A .\"ID\", round(AVG (A .AVGSPEED),2) AS avgspeed1, round(AVG (A .C_INDEX),2) AS index1, \"SUBSTR\" (A .\"TIME\", 9, 4) AS time_1 FROM INDEX_TPI_ROAD_150200_"+yearMonth+" A WHERE cast(\"SUBSTR\" (A .\"TIME\", 1, 8) as NUMBER) in (SELECT cast(replace(d.tjsd,'/','') as NUMBER) as tjsd1 from N_WORKDAYS d WHERE d.tjsd LIKE '"+yearMonth.substring(0,4)+"/"+yearMonth.substring(4)+"%' ) GROUP BY A .\"ID\", \"SUBSTR\" (A .\"TIME\", 9, 4) ORDER BY time_1 ) s, INDEX_ROAD_INFO b WHERE b. ID = s. ID";
        System.out.println(sql);
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> dataListRoadPredict(String roadid, String dateStr) {

        String sql = "SELECT a.\"ID\",a.AVGSPEED,a.C_INDEX,round((b.\"LENGTH\"/a.AVGSPEED)*3.6) as traveltime,a.\"TIME\" FROM INDEX_TPI_ROAD_150200_"+dateStr+" A,INDEX_ROAD_INFO b WHERE a.\"ID\"=b.\"ID\" AND A .\"ID\" = '"+roadid+"' AND A .\"TIME\" = (SELECT max(c.\"TIME\") from INDEX_TPI_ROAD_150200_"+dateStr+" c WHERE c.id='"+roadid+"')";

        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<String> listNoWorks() {
        String sql = "SELECT a.TJSD from N_WORKDAYS a";
        return jdbcTemplate.queryForList(sql,String.class);
    }

    @Override
    public List<Map<String, Object>> getRoadLont() {
        String sql = "SELECT * from INDEX_ROAD_LATLON ";
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public void insertTrafficFlowHour(String month) {
        String sql = "CREATE TABLE CTL_XJLL_HOUR_HISTORY_"+month+" (XJBH VARCHAR2 (32) NOT NULL,XJMC VARCHAR2 (60),XJLL VARCHAR (38),TJSD VARCHAR (38)) TABLESPACE BTJCZH ";
        System.out.println(sql);
        jdbcTemplate.execute(sql);
    }

    @Override
    public void insertTrafficFlowMin(String month) {

        String sql = "CREATE TABLE CTL_XJLL_MINUTE_HISTORY_"+month+" (XJBH VARCHAR2 (32) NOT NULL,XJMC VARCHAR2 (60),XJLL VARCHAR (38),TJSD VARCHAR (38)) TABLESPACE BTJCZH";
        System.out.println(sql);
        jdbcTemplate.execute(sql);
    }

    @Override
    public void insertTrafficFlowHourIndex1(String month) {
        String sql = "create index BTJCZH.INDEX_CTL_"+month+"_TJSD on BTJCZH.CTL_XJLL_HOUR_HISTORY_"+month+" (TJSD ASC)";
        jdbcTemplate.execute(sql);
    }

    @Override
    public void insertTrafficFlowHourIndex2(String month) {
        String sql =  "create index BTJCZH.INDEX_CTL_"+month+"_XJBH on BTJCZH.CTL_XJLL_HOUR_HISTORY_"+month+" (XJBH ASC)";
        jdbcTemplate.execute(sql);
    }

    @Override
    public void insertTrafficFlowMinIndex1(String month) {
        String sql = "create index BTJCZH.INDEX_CTL_"+month+"_MINUTE_TJSD on BTJCZH.CTL_XJLL_MINUTE_HISTORY_"+month+" (TJSD ASC)";
        jdbcTemplate.execute(sql);
    }

    @Override
    public void insertTrafficFlowMinIndex2(String month) {
        String sql = "create index BTJCZH.INDEX_CTL_"+month+"_MINUTE_XJBH on BTJCZH.CTL_XJLL_MINUTE_HISTORY_"+month+" (XJBH ASC)";
        jdbcTemplate.execute(sql);
    }

    /**
     * 查询实时流量数据
     *
     * @param timeSet  时间集合
     * @param kkbh     时间频率
     * @param flowRate 时间频率
     * @return
     */
    public JSONObject getKakoullByTimeSetKkbh(TreeSet<String> timeSet, String kkbh, String flowRate) {

        String timetep = new SimpleDateFormat("yyyyMM").format(new Date());

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String sql;
        if (flowRate.equals("60")) {
            sql = "select c.xjll,c.tjsd from ctl_xjll_hour_history_" + timetep + " c " +
                    "where c.xjbh=? and c.tjsd IN" +
                    " (" + org.apache.commons.lang.StringUtils.repeat("?", ",", timeSet.size()) + ") ORDER BY c.tjsd";
        } else {
            sql = "select c.xjll,c.tjsd from ctl_xjll_minute_history_" + timetep + " c " +
                    "where c.xjbh=? and c.tjsd IN " +
                    "(" + org.apache.commons.lang.StringUtils.repeat("?", ",", timeSet.size()) + ") ORDER BY c.tjsd";
        }
        JSONObject jsonObject = new JSONObject();
        try {
            connection = jdbcTemplate.getDataSource().getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1, kkbh);
            Iterator<String> iterator = timeSet.iterator();
            int count = 2;
            while (iterator.hasNext()) {
                String next = iterator.next();
                statement.setString(count, next);
                count++;
            }
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int xjll = resultSet.getInt("xjll");
                String tjsd = resultSet.getString("tjsd");
                jsonObject.put(tjsd, xjll);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {resultSet.close();}
                if (statement != null) {statement.close();
                }if (connection != null) {connection.close();}
            } catch (Exception e) {
                e.getStackTrace();
            }
        }
        return jsonObject;
    }


    /**
     * 从MongoDB中查询筛选条件下的历史平均值和历史峰值
     *
     * @param timeSet  时刻集合
     * @param monthSet 月份集合
     * @param dateType 日期类型
     * @param flowRate 时间频率
     * @param kkbh     卡口编号
     * @return
     */
    public JSONObject getAvgMaxKakouLlByTimeKkbh(Set<String> timeSet, Set<String> monthSet, String dateType, String flowRate, String kkbh) {
        JSONObject jsonObject = new JSONObject();
        if (flowRate.equals("5") || flowRate.equals("10")) {
            DBCollection col;
                if (dateType.equals("work")){
                 col =  mongoTemplate.getCollection("kakouFlowMinuteWork");
                }else if (dateType.equals("nwork")){
                    col =  mongoTemplate.getCollection("kakouFlowMinuteNWork");
                }else {
                    col =  mongoTemplate.getCollection("kakouFlowMinuteHoli");
                }
                BasicDBList dbList = new BasicDBList();
                BasicDBList dbList2 = new BasicDBList();
            Iterator<String> iterator = monthSet.iterator();
            while (iterator.hasNext()){
                String next = iterator.next();
                dbList2.add(next);
            }
            Iterator<String> iterator2 = timeSet.iterator();
                while (iterator2.hasNext()) {
                    String next2 = iterator2.next();
                    dbList.add(next2.split(" ")[1]);
                }
                DBObject basicDBObject = new BasicDBObject("$in", dbList);
                DBObject basicDBObject2 = new BasicDBObject("$in", dbList2);
                BasicDBObject queryDBObject = new BasicDBObject();
                queryDBObject.append("kkbh", kkbh);
                queryDBObject.append("time", basicDBObject);
                queryDBObject.append("date", basicDBObject2);

                DBCursor cursor = col.find(queryDBObject);

                while (cursor.hasNext()) {
                    DBObject rticObj = cursor.next();
                    int avgKkll = (int) rticObj.get("avgKkll");
                    int maxKkll = (int) rticObj.get("maxKkll");
                    String time = (String) rticObj.get("time");
                    if (jsonObject.containsKey(time)){
                        JSONObject json = jsonObject.getJSONObject(time);
                        int realAvgKkll = json.getInteger("avgKkll");
                        int realMaxKkll = json.getInteger("maxKkll");
                        realAvgKkll = (realAvgKkll + avgKkll)/2;
                        json.put("avgKkll",realAvgKkll);
                        if (realMaxKkll>maxKkll){
                            json.put("maxKkll",realMaxKkll);
                        }else {
                            json.put("maxKkll",maxKkll);
                        }
                        jsonObject.put(time,json);
                    }else {
                        JSONObject json = new JSONObject();
                        json.put("avgkkll", avgKkll);
                        json.put("maxkkll", maxKkll);
                        jsonObject.put(time,json);
                    }
                }
        } else if (flowRate.equals("60")) {

                DBCollection col = mongoTemplate.getCollection("kakouFlowHour" );
                BasicDBList dbList = new BasicDBList();
                BasicDBList dbList2 = new BasicDBList();

                Iterator<String> iterator = monthSet.iterator();
                while (iterator.hasNext()){
                    String next = iterator.next();
                    dbList2.add(next);
                }
                Iterator<String> iterator2 = timeSet.iterator();
                while (iterator2.hasNext()) {
                    String next2 = iterator2.next();
                    dbList.add(next2.split(" ")[1]);
                }
                DBObject basicDBObject = new BasicDBObject("$in", dbList);
                DBObject basicDBObject2 = new BasicDBObject("$in", dbList2);

                BasicDBObject queryDBObject = new BasicDBObject();
                queryDBObject.append("kkbh", kkbh);
                queryDBObject.append("time", basicDBObject);
                queryDBObject.append("date", basicDBObject2);
                queryDBObject.append("dateType", dateType);

                DBCursor cursor = col.find(queryDBObject);
                while (cursor.hasNext()) {
                    DBObject rticObj = cursor.next();

                    int avgKkll = (int) rticObj.get("avgKkll");
                    int maxKkll = (int) rticObj.get("maxKkll");
                    String time = (String) rticObj.get("time");
                    if (jsonObject.containsKey(time)){
                        JSONObject json = jsonObject.getJSONObject(time);
                        int realAvgKkll = json.getInteger("avgKkll");
                        int realMaxKkll = json.getInteger("maxKkll");
                        realAvgKkll = (realAvgKkll+avgKkll)/2;
                        json.put("avgKkll",realAvgKkll);
                        if (realMaxKkll>maxKkll){
                            json.put("maxKkll",realMaxKkll);
                        }else {
                            json.put("maxKkll",maxKkll);
                        }
                        jsonObject.put(time,json);
                    }else {
                        JSONObject json = new JSONObject();
                        json.put("avgkkll", avgKkll);
                        json.put("maxkkll", maxKkll);
                        jsonObject.put(time,json);
                    }
                }
        }
        JSONObject kakouAvgMaxJson = new JSONObject();
            Iterator<String> iterator = timeSet.iterator();
            while (iterator.hasNext()) {//遍历时间的有序集合
                String next = iterator.next();
                if (jsonObject.containsKey(next.split(" ")[1])){
                    JSONObject dmJson = jsonObject.getJSONObject(next.split(" ")[1]);
                    int realAvgDM = dmJson.getInteger("avgkkll");
                    int realMaxDM = dmJson.getInteger("maxkkll");
                    kakouAvgMaxJson.put(next.split(" ")[1], realAvgDM + "," + realMaxDM);
                }
            }
        return kakouAvgMaxJson;
    }

    public JSONObject getAvgMaxFromMongo(TreeSet<String> timeSet,String deviceid,String dateType){

            JSONObject jsonObject = new JSONObject();
            DBCollection col = mongoTemplate.getCollection("kakouFlow");
            BasicDBList dbList = new BasicDBList();
            Iterator<String> iterator2 = timeSet.iterator();
            while (iterator2.hasNext()) {
                String next2 = iterator2.next();
                dbList.add(next2.split(" ")[1]);
            }
            DBObject basicDBObject = new BasicDBObject("$in", dbList);
            BasicDBObject queryDBObject = new BasicDBObject();
            queryDBObject.append("kkbh", deviceid);
            queryDBObject.append("time", basicDBObject);
            queryDBObject.append("dateType", dateType);
            queryDBObject.append("timeType", "Hour");
            DBCursor cursor = col.find(queryDBObject);
            while (cursor.hasNext()) {
                DBObject rticObj = cursor.next();
                JSONObject json = new JSONObject();
                int avgKkll = (int) rticObj.get("avgKkll");
                int maxKkll = (int) rticObj.get("maxKkll");
                String time = (String) rticObj.get("time");
                json.put("avgkkll", avgKkll);
                json.put("maxkkll", maxKkll);
                jsonObject.put(time, json);
            }
        return jsonObject;
    }
}